<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <meta name="generator" content="VuePress 2.0.0-beta.27">
    <meta http-equiv="Content-Security-Policy" content="upgrade-insecure-requests"><script src="https://at.alicdn.com/t/font_2849934_v6y652peian.js"></script><link href="https://gitee.com/ran_yong/mark-down-table-upload/raw/master/img/20211123222242.png" rel="icon"><link rel="stylesheet" type="text/css" href="//at.alicdn.com/t/font_2970797_5eiedcvhf0a.css"><title>SQL基础学习 | Sakura</title><meta name="description" content="">
    <link rel="preload" href="/assets/js/runtime~app.5b846f34.js" as="script"><link rel="preload" href="/assets/css/styles.f5148030.css" as="style"><link rel="preload" href="/assets/js/452.9ec739fe.js" as="script"><link rel="preload" href="/assets/js/app.05eaf2e4.js" as="script">
    <link rel="stylesheet" href="/assets/css/styles.f5148030.css">
  </head>
  <body>
    <div id="app"><!--[--><!--[--><div class="common" style="--borderRadius:10px;--opacity:1;--fontColor:#2c3e50;--fontFamily:-apple-system;--fitter-blue:1px;"><header style="--borderRadius:10px;--opacity:1;--fontColor:#2c3e50;--fontFamily:-apple-system;--fitter-blue:1px;" class="header-bg-show navbar" id="c-navbar"><div class="toggle-sidebar-button"><svg class="icon" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div><span><a href="/" class=""><img style="border-radius:30px;" class="logo" src="https://gitee.com/ran_yong/mark-down-table-upload/raw/master/img/20211123222242.png" alt="Sakura"><span class="site-name" style="color: #2c3e50;">Sakura</span></a></span><div class="navbar-links-wrapper" style=""><!--[--><!--]--><nav class="navbar-links can-hide"><!--[--><div class="navbar-links-item"><!--[--><span class="aurora-nav-font aurora-navbar-icon"></span><a aria-current="page" href="/Back-end/SQL%E5%9F%BA%E7%A1%80%E5%AD%A6%E4%B9%A0.html#" class="router-link-active router-link-exact-active nav-link nav-link-active" aria-label="面经">面经</a><!--]--></div><div class="navbar-links-item"><div class="dropdown-wrapper"><span class="sakura-html aurora-nav-font aurora-navbar-icon"></span><button class="dropdown-title" type="button" aria-label="前端"><span class="title">前端</span><span class="arrow down"></span></button><button class="mobile-dropdown-title" type="button" aria-label="前端"><span class="title">前端</span><span class="right arrow"></span></button><ul style="display:none;" id="c-nav-dropdown" class="nav-dropdown"><!--[--><li class="dropdown-item"><!--[--><h4 class="dropdown-subtitle"><span>JavaScript</span></h4><ul class="dropdown-subitem-wrapper"><!--[--><li class="dropdown-subitem"><!--[--><!----><a href="/Front-end/JavaScript%E8%AF%AD%E6%B3%95%E5%AD%A6%E4%B9%A0.html" class="nav-link" aria-label="JavaScript语言">JavaScript语言</a><!--]--></li><!--]--></ul><!--]--></li><li class="dropdown-item"><!--[--><h4 class="dropdown-subtitle"><span>TypeScript</span></h4><ul class="dropdown-subitem-wrapper"><!--[--><li class="dropdown-subitem"><!--[--><!----><a href="/Front-end/TypeScript%E8%AF%AD%E6%B3%95%E5%AD%A6%E4%B9%A0.html" class="nav-link" aria-label="TS语法学习">TS语法学习</a><!--]--></li><!--]--></ul><!--]--></li><li class="dropdown-item"><!--[--><h4 class="dropdown-subtitle"><span>Vue项目实战</span></h4><ul class="dropdown-subitem-wrapper"><!--[--><li class="dropdown-subitem"><!--[--><!----><a href="/Front-end/Vue%E9%A1%B9%E7%9B%AE%E5%AE%9E%E6%88%98.html" class="nav-link" aria-label="Vue_shop 项目实战">Vue_shop 项目实战</a><!--]--></li><!--]--></ul><!--]--></li><!--]--></ul></div></div><div class="navbar-links-item"><div class="dropdown-wrapper"><span class="sakura-houduankaifa aurora-nav-font aurora-navbar-icon"></span><button class="dropdown-title" type="button" aria-label="后端"><span class="title">后端</span><span class="arrow down"></span></button><button class="mobile-dropdown-title" type="button" aria-label="后端"><span class="title">后端</span><span class="right arrow"></span></button><ul style="display:none;" id="c-nav-dropdown" class="nav-dropdown"><!--[--><li class="dropdown-item"><!--[--><h4 class="dropdown-subtitle"><span>Fastapi</span></h4><ul class="dropdown-subitem-wrapper"><!--[--><li class="dropdown-subitem"><!--[--><!----><a href="/Back-end/Fastapi.html" class="nav-link" aria-label="Fastapi学习">Fastapi学习</a><!--]--></li><!--]--></ul><!--]--></li><li class="dropdown-item"><!--[--><h4 class="dropdown-subtitle"><span>Python基础</span></h4><ul class="dropdown-subitem-wrapper"><!--[--><li class="dropdown-subitem"><!--[--><!----><a href="/Back-end/python%E5%9F%BA%E7%A1%80%E5%AD%A6%E4%B9%A0.html" class="nav-link" aria-label="python基础学习">python基础学习</a><!--]--></li><!--]--></ul><!--]--></li><li class="dropdown-item"><!--[--><h4 class="dropdown-subtitle"><span>Go基础</span></h4><ul class="dropdown-subitem-wrapper"><!--[--><li class="dropdown-subitem"><!--[--><!----><a href="/Back-end/Go%E5%9F%BA%E7%A1%80%E5%AD%A6%E4%B9%A0.html" class="nav-link" aria-label="Go基础学习">Go基础学习</a><!--]--></li><!--]--></ul><!--]--></li><li class="dropdown-item"><!--[--><h4 class="dropdown-subtitle"><span>SQL基础</span></h4><ul class="dropdown-subitem-wrapper"><!--[--><li class="dropdown-subitem"><!--[--><!----><a aria-current="page" href="/Back-end/SQL%E5%9F%BA%E7%A1%80%E5%AD%A6%E4%B9%A0.html" class="router-link-active router-link-exact-active nav-link" aria-label="SQL基础学习">SQL基础学习</a><!--]--></li><!--]--></ul><!--]--></li><!--]--></ul></div></div><div class="navbar-links-item"><div class="dropdown-wrapper"><span class="sakura-service aurora-nav-font aurora-navbar-icon"></span><button class="dropdown-title" type="button" aria-label="服务端"><span class="title">服务端</span><span class="arrow down"></span></button><button class="mobile-dropdown-title" type="button" aria-label="服务端"><span class="title">服务端</span><span class="right arrow"></span></button><ul style="display:none;" id="c-nav-dropdown" class="nav-dropdown"><!--[--><li class="dropdown-item"><!--[--><h4 class="dropdown-subtitle"><span>宝塔面板</span></h4><ul class="dropdown-subitem-wrapper"><!--[--><li class="dropdown-subitem"><!--[--><!----><a href="/Other/WebHook.html" class="nav-link" aria-label="宝塔面板自动同步gitee仓库代码">宝塔面板自动同步gitee仓库代码</a><!--]--></li><!--]--></ul><!--]--></li><li class="dropdown-item"><!--[--><h4 class="dropdown-subtitle"><span>配置公钥</span></h4><ul class="dropdown-subitem-wrapper"><!--[--><li class="dropdown-subitem"><!--[--><!----><a href="/Other/%E9%85%8D%E7%BD%AEgitee%E3%80%81github%E5%85%AC%E9%92%A5.html" class="nav-link" aria-label="配置gitee、github公钥">配置gitee、github公钥</a><!--]--></li><!--]--></ul><!--]--></li><li class="dropdown-item"><!--[--><h4 class="dropdown-subtitle"><span>宝塔配置HTTPS</span></h4><ul class="dropdown-subitem-wrapper"><!--[--><li class="dropdown-subitem"><!--[--><!----><a href="/Other/%E5%AE%9D%E5%A1%94%E9%85%8D%E7%BD%AEHTTPS.html" class="nav-link" aria-label="如何通过宝塔配置HTTPS网站">如何通过宝塔配置HTTPS网站</a><!--]--></li><!--]--></ul><!--]--></li><li class="dropdown-item"><!--[--><h4 class="dropdown-subtitle"><span>Node后台运行</span></h4><ul class="dropdown-subitem-wrapper"><!--[--><li class="dropdown-subitem"><!--[--><!----><a href="/Other/%E5%9C%A8linux%E4%B8%AD%E6%8C%81%E7%BB%AD%E8%BF%90%E8%A1%8CNode.js%E6%96%87%E4%BB%B6.html" class="nav-link" aria-label="在linux中持续运行Node.js文件">在linux中持续运行Node.js文件</a><!--]--></li><!--]--></ul><!--]--></li><li class="dropdown-item"><!--[--><h4 class="dropdown-subtitle"><span>Mac安装Scrcpy投屏软件</span></h4><ul class="dropdown-subitem-wrapper"><!--[--><li class="dropdown-subitem"><!--[--><!----><a href="/Other/Mac%E5%AE%89%E8%A3%85scrcpy%E6%8A%95%E5%B1%8F%E8%BD%AF%E4%BB%B6.html" class="nav-link" aria-label="Mac M1芯 安装scrcpy投屏软件">Mac M1芯 安装scrcpy投屏软件</a><!--]--></li><!--]--></ul><!--]--></li><!--]--></ul></div></div><div class="navbar-links-item"><!--[--><span class="sakura-shijianzhou aurora-nav-font aurora-navbar-icon"></span><a href="/aurora-archive/" class="nav-link nav-link-active" aria-label="时间轴">时间轴</a><!--]--></div><div class="navbar-links-item"><!--[--><span class="sakura-guanyuwomen aurora-nav-font aurora-navbar-icon"></span><a href="/about" class="nav-link nav-link-active" aria-label="关于我">关于我</a><!--]--></div><div class="navbar-links-item"><!--[--><span class="sakura-youlianguanli aurora-nav-font aurora-navbar-icon"></span><a href="/link" class="nav-link nav-link-active" aria-label="友链">友链</a><!--]--></div><div class="navbar-links-item"><!--[--><span class="sakura-biaoqian aurora-nav-font aurora-navbar-icon"></span><a href="/tag" class="nav-link nav-link-active" aria-label="标签">标签</a><!--]--></div><div class="navbar-links-item"><!--[--><span class="sakura-41shuoshuo aurora-nav-font aurora-navbar-icon"></span><a href="/mood" class="nav-link nav-link-active" aria-label="说说">说说</a><!--]--></div><div class="navbar-links-item"><!--[--><span class="sakura-xiangce aurora-nav-font aurora-navbar-icon"></span><a href="/photo" class="nav-link nav-link-active" aria-label="相册">相册</a><!--]--></div><div class="navbar-links-item"><!--[--><span class="sakura-github aurora-nav-font aurora-navbar-icon"></span><a class="nav-link external nav-link-active" href="https://github.com/ranyong1997/sakura-docs" rel="noopener noreferrer" target="_blank" aria-label="Sakura"><!--[--><!--]--> Sakura<!--[--><!--]--></a><!--]--></div><!--]--></nav><!--[--><!--]--><!----><form class="search-box" role="search"><input type="search" autocomplete="off" spellcheck="false" value><!----></form></div></header><!--[--><div id="" class="mobile-sidebar-control"></div><div id="" class="mobileAnimate mobile-sidebar"><!----></div><!--]--><div class="setColor" style="--borderRadius:10px;--fontFamily:-apple-system;--fontColor:#2c3e50;"><div id="setIco" class="setIco bg_color"></div><div class="welcome-parent" id="welcome-parent"><div class="custom-about m-4 p-4 f4 color-shadow-small bg-gray-800-mktg rounded-2 signup-content-container welcome" style="display: none;" id="welcome"><span class="cancel aurora-iconfont-common aurora-style-cancel" id="cancel"></span><h1 class="common-style" id="sr-only-h2"></h1><div class="custom-top custom-common"><div class="custom-top-span custom-common-span"><span class="aurora-iconfont-common aurora-style-img home-welcome-custom-icon"></span>  </div><div class="custom-top-span custom-common-span"><span class="aurora-iconfont-common aurora-style-blur home-welcome-custom-icon"></span>  </div></div><div class="custom-bottom custom-common"><div style="flex:1;" class="custom-bottom-span custom-common-span"><!--[--><li class="custom-li"><span style="background-color: #2c3e50;" data-color="#2c3e50"></span></li><li class="custom-li"><span style="background-color: #42a5f5;" data-color="#42a5f5"></span></li><li class="custom-li"><span style="background-color: #8093f1;" data-color="#8093f1"></span></li><li class="custom-li"><span style="background-color: #FF6EC7;" data-color="#FF6EC7"></span></li><li class="custom-li"><span style="background-color: #FF7F00;" data-color="#FF7F00"></span></li><li class="custom-li"><span style="background-color: #8FBC8F;" data-color="#8FBC8F"></span></li><li class="custom-li"><span style="background-color: #EAADEA;" data-color="#EAADEA"></span></li><li class="custom-li"><span style="background-color: #3299CC;" data-color="#3299CC"></span></li><!--]--></div></div><div class="custom-bottom custom-common"><div style="flex:1;" class="custom-bottom-span custom-common-span"><!--[--><li class="custom-li"><span style="font-family: -apple-system;" data-color="-apple-system">冉</span></li><li class="custom-li"><span style="font-family: hlt;" data-color="hlt">冉</span></li><li class="custom-li"><span style="font-family: tzt;" data-color="tzt">冉</span></li><li class="custom-li"><span style="font-family: sst;" data-color="sst">冉</span></li><li class="custom-li"><span style="font-family: lf;" data-color="lf">冉</span></li><li class="custom-li"><span style="font-family: xsf;" data-color="xsf">冉</span></li><li class="custom-li"><span style="font-family: lsf;" data-color="lsf">冉</span></li><li class="custom-li"><span style="font-family: cgt;" data-color="cgt">冉</span></li><!--]--></div></div><div class="custom-bottom custom-common"><div class="custom-bottom-span custom-common-span"> 圆角 </div><div style="flex:3;" class="custom-bottom-span custom-common-span"><div class="input-range"><div class="input-left"><input autocomplete min="1" max="40" value="10" step="1" type="range"></div><div class="input-right">10</div></div></div></div><div class="custom-bottom custom-common"><div class="custom-bottom-span custom-common-span"> 透明度 </div><div style="flex:3;" class="custom-bottom-span custom-common-span"><div class="input-range"><div class="input-left"><input autocomplete min="0" max="1" value="1" step="0.01" type="range"></div><div class="input-right">1</div></div></div></div></div></div></div><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--><div style="--fontColor: #2c3e50; --fontFamily: -apple-system;" class="sidebar-single-enter-animate theme-container"><div class="page-sidebar"><div class="sidebar-single-enter-animate page-top" id="page-top" show-mood-edit="false"><div class="top-mask" style="background-image: url(https://api.ixiaowai.cn/api/api.php?time=-217051.9230769231);"></div><div class="aurora-top-bubble"><div class="aurora-top-bubble-par"><div class="aurora-top-bubble-box" id="aurora-top-bubble-box"></div><canvas class="aurora-top-bubble-canvas" id="aurora-top-bubble-canvas"></canvas></div></div><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--><div class="page-record-control"><div class="page-top-record" id="page-top-record"><div class="page-record-bot-common page-record-top"><div class="page-record-top-left page-record-single-common"><span class="aurora-iconfont-common aurora-page-word"></span>  <span class="page-record-single-desc">总字数</span><span>0</span></div><div class="page-record-top-right page-record-single-common"><span class="aurora-iconfont-common aurora-page-time"></span>  <span class="page-record-single-desc">时长</span><span>0</span></div></div><div class="page-record-bot-common page-record-center"><div class="page-record-center-left page-record-single-common"><span class="aurora-iconfont-common aurora-page-comment"></span>  <span class="page-record-single-desc">评论数</span><span id="/Back-end/SQL%E5%9F%BA%E7%A1%80%E5%AD%A6%E4%B9%A0.html" class="waline-comment-count"></span></div><div class="page-record-center-right page-record-single-common"><span class="aurora-iconfont-common aurora-page-read"></span>  <span class="page-record-single-desc">总阅读数</span><span id="/Back-end/SQL%E5%9F%BA%E7%A1%80%E5%AD%A6%E4%B9%A0.html" class="waline-visitor-count"></span></div></div><!----></div></div><div class="top-image" id="top-image"><h1></h1></div></div><div class="content"><div id="article-page-parent" class="article-page-parent-pro article-page-parent"><div class="noShowSidebar page-sidebar-left" id="page-sidebar-left"><!--[--><!--]--><!--[--><!--[--><main style="--borderRadius: 10px;--opacity: 1;" class="page sidebar-single-enter-animate" id="article-page"><!--[--><!--]--><div id="theme-default-content" class="hide-h1-tag theme-default-content pageContent medium-zoom-content"><div class="page-top-share"><div class="page-top-share-next"><div class="poster-share" style="z-index:99;"><div class="poster-button" id="poster-button"><span class="aurora-iconfont-common aurora-poster-ico" data="2">生成海报3</span>  </div></div></div></div><!--[--><h1 id="sql基础学习" tabindex="-1"><a class="header-anchor" href="#sql基础学习" aria-hidden="true">#</a> SQL基础学习</h1><p>前言：</p><blockquote><p>SQL是用于访问和处理数据库的标准的计算机语言。</p></blockquote><p><code>什么是SQL？</code></p><ul><li>SQL指结构化查询语言</li><li>SQL使我们有能力访问数据库</li><li>SQL有一种ANSI<a href="https://baike.baidu.com/item/ANSI%E6%A0%87%E5%87%86/3127116?fr=aladdin" target="_blank" rel="noopener noreferrer">ANSI<span><svg class="icon outbound" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewbox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><!--[--><span class="sr-only">open in new window</span><!--]--></span></a>的标准计算机语言</li></ul><p><code>SQL能做什么？</code></p><ul><li>SQL面向数据库执行查询</li><li>SQL可从数据库取回数据</li><li>SQL可在数据库中插入新的记录</li><li>SQL可更新数据库中的数据</li><li>SQL可从数据库删除记录</li><li>SQL可创建新数据库</li><li>SQL可在数据库中创建新表</li><li>SQL可在数据库中创建存储过程</li><li>SQL可在数据库中创建视图</li><li>SQL可以设置表、存储过程中和视图的权限</li></ul><h2 id="sql语法" tabindex="-1"><a class="header-anchor" href="#sql语法" aria-hidden="true">#</a> SQL语法</h2><h3 id="数据库表" tabindex="-1"><a class="header-anchor" href="#数据库表" aria-hidden="true">#</a> 数据库表</h3><blockquote><p>一个数据库通常包含一个或多个表。每个表由一个名字标识。表包含带有数据的记录</p></blockquote><table><thead><tr><th>id</th><th>LastName</th><th>FirstName</th><th>Address</th></tr></thead><tbody><tr><td>1</td><td>Adams</td><td>John</td><td>Oxford Street</td></tr><tr><td>2</td><td>Bush</td><td>George</td><td>Fifth Avenue</td></tr></tbody></table><h3 id="sql语句" tabindex="-1"><a class="header-anchor" href="#sql语句" aria-hidden="true">#</a> SQL语句</h3><p>下面语句从表中选取LastName列的数据：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> LastName <span class="token keyword">FROM</span> Persons
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>运行结果：</p><table><thead><tr><th>LastName</th></tr></thead><tbody><tr><td>Adams</td></tr><tr><td>Bush</td></tr></tbody></table><div class="custom-container tip"><p class="custom-container-title">TIP</p><p>注意：SQL对大小写不敏感！</p></div><h3 id="sql-dml-和-ddl" tabindex="-1"><a class="header-anchor" href="#sql-dml-和-ddl" aria-hidden="true">#</a> SQL DML 和 DDL</h3><p>可以把SQL分为两个部分：数据操作语言(<strong>DML</strong>)和数据定义语言(<strong>DDL</strong>)</p><p>SQL(结构化查询语言)是用于执行查询的语法。但是SQL语言也包含用于更新、插入和删除记录的语法。</p><p>查询和更新指令构成了 SQL 的 DML 部分：</p><ul><li><em>SELECT</em> - 从数据库表中获取数据</li><li><em>UPDATE</em> - 更新数据库表中的数据</li><li><em>DELETE</em> - 从数据库表中删除数据</li><li><em>INSERT INTO</em> - 向数据库表中插入数据</li></ul><p>SQL 的数据定义语言(DDL) 部分使我们有能力创建和删除表格。我们也可以定义索引(键)，规定表之间的链接，以及施加表间的约束。</p><p>SQL中最重要的DDL语句：</p><ul><li><em>CREATE DATABASE</em> - 创建新数据库</li><li><em>ALTER DATABASE</em> - 修改数据库</li><li><em>CREATE TABLE</em> - 创建新表</li><li><em>ALTER TABLE</em> - 变更（改变）数据库表</li><li><em>DROP TABLE</em> - 删除表</li><li><em>CREATE INDEX</em> - 创建索引（搜索键）</li><li><em>DROP INDEX</em> - 删除索引</li></ul><h2 id="sql-select" tabindex="-1"><a class="header-anchor" href="#sql-select" aria-hidden="true">#</a> SQL select</h2><h3 id="sql-select-语句" tabindex="-1"><a class="header-anchor" href="#sql-select-语句" aria-hidden="true">#</a> SQL SELECT 语句</h3><blockquote><p>SELECT 语句用于从表中选取数据，结果被存储在一个结果表中</p></blockquote><h4 id="sql-select-语法" tabindex="-1"><a class="header-anchor" href="#sql-select-语法" aria-hidden="true">#</a> SQL SELECT 语法</h4><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> 列名称 <span class="token keyword">FROM</span> 表名称
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>以及：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> 表名称
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h2 id="sql-select-distinct-语句" tabindex="-1"><a class="header-anchor" href="#sql-select-distinct-语句" aria-hidden="true">#</a> SQL SELECT DISTINCT 语句</h2><h3 id="sql-select-distinct-语句-1" tabindex="-1"><a class="header-anchor" href="#sql-select-distinct-语句-1" aria-hidden="true">#</a> SQL SELECT DISTINCT 语句</h3><blockquote><p>在表中，可能会包含重复值。关键字DISTINCT 用于返回唯一不同的值</p></blockquote><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token keyword">DISTINCT</span> 列名称 <span class="token keyword">FROM</span> 表名称
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h2 id="sql-where-子句" tabindex="-1"><a class="header-anchor" href="#sql-where-子句" aria-hidden="true">#</a> SQL WHERE 子句</h2><blockquote><p>WHERE 自居用于规定选择的标准</p></blockquote><h3 id="where-子句" tabindex="-1"><a class="header-anchor" href="#where-子句" aria-hidden="true">#</a> WHERE 子句</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> 列名称 <span class="token keyword">FROM</span> 表名称 <span class="token keyword">WHERE</span> 列 运算符 值
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>运算符有一下：</p><table><thead><tr><th>操作符</th><th>描述</th></tr></thead><tbody><tr><td>=</td><td>等于</td></tr><tr><td>!=</td><td>不等于</td></tr><tr><td>&gt;</td><td>大于</td></tr><tr><td>&lt;</td><td>小于</td></tr><tr><td>&lt;=</td><td>小于等于</td></tr><tr><td>&gt;=</td><td>大于等于</td></tr><tr><td>BETWEEN</td><td>在某个范围内</td></tr><tr><td>LIKE</td><td>搜索某种模式</td></tr></tbody></table><p>举个例子：</p><p>如果只希望选取居住在城市&quot;Beijing&quot;的人，我们需要向SELECT语句添加WHERE子句：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> Person <span class="token keyword">WHERE</span> City <span class="token operator">=</span> <span class="token string">&#39;Beijing&#39;</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><div class="custom-container tip"><p class="custom-container-title">TIP</p><p>注意：我们在例子中的条件值周围使用的是单引号。</p><p>SQL使用单引号来环绕文本值。如果是<strong>数值</strong>，请不要使用引号。</p></div><p><code>文本值</code></p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> Persons <span class="token keyword">WHERE</span> FirstName<span class="token operator">=</span><span class="token string">&#39;Bush&#39;</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p><code>数值</code></p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> Persons <span class="token keyword">WHERE</span> <span class="token keyword">Year</span> <span class="token operator">&gt;</span> <span class="token number">1965</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h2 id="sql-and-or-运算符" tabindex="-1"><a class="header-anchor" href="#sql-and-or-运算符" aria-hidden="true">#</a> SQL AND&amp;OR 运算符</h2><blockquote><p>AND 和 OR 运算符用于基于一个以上的条件对记录进行过滤</p></blockquote><h4 id="and-和-or-运算符" tabindex="-1"><a class="header-anchor" href="#and-和-or-运算符" aria-hidden="true">#</a> AND 和 OR 运算符</h4><blockquote><p>AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来。</p><p>如果第一个条件和第二个条件都成立，则AND运算符显示一条记录</p><p>如果第一个条件和第二个条件中只要有一个成立，则OR运算符显示一条记录</p></blockquote><p>举个例子：</p><h5 id="and-运算符实例" tabindex="-1"><a class="header-anchor" href="#and-运算符实例" aria-hidden="true">#</a> AND 运算符实例</h5><p>使用 AND 来显示所有姓为 &quot;Carter&quot; 并且名为 &quot;Thomas&quot; 的人：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> Persons <span class="token keyword">WHERE</span> FfrstName<span class="token operator">=</span><span class="token string">&#39;Thomas&#39;</span> <span class="token operator">AND</span> LastName<span class="token operator">=</span><span class="token string">&#39;Carter&#39;</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h5 id="or-运算符实例" tabindex="-1"><a class="header-anchor" href="#or-运算符实例" aria-hidden="true">#</a> OR 运算符实例</h5><p>使用 OR 来显示所有姓为 &quot;Carter&quot; 或者名为 &quot;Thomas&quot; 的人：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> Persons <span class="token keyword">WHERE</span> firstname<span class="token operator">=</span><span class="token string">&#39;Thomas&#39;</span> <span class="token operator">OR</span> lastname<span class="token operator">=</span><span class="token string">&#39;Carter&#39;</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h5 id="结合-and-和-or-运算符" tabindex="-1"><a class="header-anchor" href="#结合-and-和-or-运算符" aria-hidden="true">#</a> 结合 AND 和 OR 运算符</h5><p>我们也可以把 AND 和 OR 结合起来（使用圆括号来组成复杂的表达式）:</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> Persons <span class="token keyword">WHERE</span> <span class="token punctuation">(</span>FirstName<span class="token operator">=</span><span class="token string">&#39;Thomas&#39;</span> <span class="token operator">or</span> FirstName<span class="token operator">=</span><span class="token string">&#39;William&#39;</span><span class="token punctuation">)</span> <span class="token operator">AND</span> LastName<span class="token operator">=</span><span class="token string">&#39;Carter&#39;</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h2 id="sql-order-by-子句" tabindex="-1"><a class="header-anchor" href="#sql-order-by-子句" aria-hidden="true">#</a> SQL ORDER BY 子句</h2><h3 id="order-by-语句" tabindex="-1"><a class="header-anchor" href="#order-by-语句" aria-hidden="true">#</a> ORDER BY 语句</h3><ul><li>ORDER BY 语句用于根据指定的列对结果集进行排序</li><li>ORDER BY 语句默认按照升起对记录进行排序</li></ul><p>如果你希望按照降序对记录进行排序，可以使用 DESC 关键字</p><p>举个例子：</p><p>以字母顺序显示公司名称：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> Company<span class="token punctuation">,</span> OrderNumber <span class="token keyword">FROM</span> Orders <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> Company
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>以字母顺序显示公司名称（Company），并以数字顺序显示顺序号（OrderNumber）：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> Company<span class="token punctuation">,</span>OrderNumber <span class="token keyword">FROM</span> Orders <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> Company<span class="token punctuation">,</span>OrderNumber
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>以逆字母顺序显示公司名称：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> Company<span class="token punctuation">,</span>OrderNumber <span class="token keyword">FROM</span> Orders ORDERS <span class="token keyword">BY</span> Company <span class="token keyword">DESC</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>以逆字母顺序显示公司名称，并以数字顺序显示顺序号：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> Company<span class="token punctuation">,</span>OrderNumber <span class="token keyword">FROM</span> Orders <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> Company <span class="token keyword">DESC</span><span class="token punctuation">,</span>OrderNumber <span class="token keyword">ASC</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h2 id="sql-insert-into-语句" tabindex="-1"><a class="header-anchor" href="#sql-insert-into-语句" aria-hidden="true">#</a> SQL INSERT INTO 语句</h2><blockquote><p>INSERT INTO 语句用于向表格中插入新的行</p></blockquote><h3 id="insert-info-语句" tabindex="-1"><a class="header-anchor" href="#insert-info-语句" aria-hidden="true">#</a> INSERT INFO 语句</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> 表名称 <span class="token keyword">VALUES</span><span class="token punctuation">(</span>值<span class="token number">1</span>，值<span class="token number">2</span>，<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>我们也可以指定所要插入数据的列：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> table_name<span class="token punctuation">(</span>列<span class="token number">1</span>，列<span class="token number">2</span>，<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span>值<span class="token number">1</span>，值<span class="token number">2</span>，<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>举个例子：</p><p>插入新的行：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> Persons <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">&#39;Gates&#39;</span><span class="token punctuation">,</span><span class="token string">&#39;Bill&#39;</span><span class="token punctuation">,</span><span class="token string">&#39;Xuanwumen 10&#39;</span><span class="token punctuation">,</span><span class="token string">&#39;Beijing&#39;</span><span class="token punctuation">)</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>在指定的列中插入数据：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> Persons<span class="token punctuation">(</span>LastName<span class="token punctuation">,</span>Address<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">&#39;Wilson&#39;</span><span class="token punctuation">,</span><span class="token string">&#39;Champs-Elysess&#39;</span><span class="token punctuation">)</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h2 id="sql-update-语句" tabindex="-1"><a class="header-anchor" href="#sql-update-语句" aria-hidden="true">#</a> SQL UPDATE 语句</h2><blockquote><p>Update 语句用于修改表中的数据</p></blockquote><h3 id="update-语句" tabindex="-1"><a class="header-anchor" href="#update-语句" aria-hidden="true">#</a> UPDATE 语句</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">UPDATE</span> 表名称 <span class="token keyword">SET</span> 列名称 <span class="token operator">=</span> 新值 <span class="token keyword">WHERE</span> 列名称 <span class="token operator">=</span> 某值
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>举个例子：</p><p>更新某一行的一个列</p><p>我们为 lastname 是 &quot;Wilson&quot; 的人添加 firstname：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">UPDATE</span> Person <span class="token keyword">SET</span> FirstName <span class="token operator">=</span> <span class="token string">&#39;Fred&#39;</span> <span class="token keyword">WHERE</span> LastName <span class="token operator">=</span> <span class="token string">&#39;Wilson&#39;</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>更新某一行中的若干列</p><p>我们会修改地址（address），并添加城市名称（city）：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">UPDATE</span> Person <span class="token keyword">SET</span> Address <span class="token operator">=</span> <span class="token string">&#39;Zhongshan 23&#39;</span><span class="token punctuation">,</span>City <span class="token operator">=</span> <span class="token string">&#39;Nanjing&#39;</span> <span class="token keyword">WHERE</span> LastName <span class="token operator">=</span> <span class="token string">&#39;Wilson&#39;</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h2 id="sql-delete-语句" tabindex="-1"><a class="header-anchor" href="#sql-delete-语句" aria-hidden="true">#</a> SQL DELETE 语句</h2><blockquote><p>DELETE 语句用于删除表中的列</p></blockquote><h3 id="delete-语句" tabindex="-1"><a class="header-anchor" href="#delete-语句" aria-hidden="true">#</a> DELETE 语句</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> 表名称 <span class="token keyword">WHERE</span> 列名称 <span class="token operator">=</span> 值
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>举个例子：</p><p>删除某行</p><p>&quot;Fred Wilson&quot; 会被删除：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> Person <span class="token keyword">WHERE</span> LastName <span class="token operator">=</span> <span class="token string">&#39;Wilson&#39;</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>删除所有行</p><p>可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> table_name
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>或者：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DELETE</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> table_name
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h2 id="sql-top-子句" tabindex="-1"><a class="header-anchor" href="#sql-top-子句" aria-hidden="true">#</a> SQL TOP 子句</h2><blockquote><p>TOP 子句用于规定要返回的记录的数目。</p></blockquote><h3 id="sql-server-语法" tabindex="-1"><a class="header-anchor" href="#sql-server-语法" aria-hidden="true">#</a> SQL Server 语法：</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token keyword">TOP</span> number <span class="token operator">|</span> <span class="token keyword">percent</span> column_name<span class="token punctuation">(</span>s<span class="token punctuation">)</span> <span class="token keyword">FROM</span> table_name
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h3 id="mysql-和-oracle-中的-sql-select-top-是等价的" tabindex="-1"><a class="header-anchor" href="#mysql-和-oracle-中的-sql-select-top-是等价的" aria-hidden="true">#</a> MySQL 和 Oracle 中的 SQL SELECT TOP 是等价的</h3><h5 id="mysql-语法" tabindex="-1"><a class="header-anchor" href="#mysql-语法" aria-hidden="true">#</a> MySQL 语法</h5><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> column_name<span class="token punctuation">(</span>s<span class="token punctuation">)</span> <span class="token keyword">FROM</span> table_name <span class="token keyword">LIMIT</span> number
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>例子：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> Persons <span class="token keyword">LIMIT</span> <span class="token number">5</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h5 id="oracle-语法" tabindex="-1"><a class="header-anchor" href="#oracle-语法" aria-hidden="true">#</a> Oracle 语法</h5><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> column_name<span class="token punctuation">(</span>s<span class="token punctuation">)</span> <span class="token keyword">FROM</span> table_name <span class="token keyword">WHERE</span> ROWNUM <span class="token operator">&lt;=</span> number
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>例子：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> Persons <span class="token keyword">WHERE</span> ROWNUM <span class="token operator">&lt;=</span> <span class="token number">5</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h3 id="sql-top-实例" tabindex="-1"><a class="header-anchor" href="#sql-top-实例" aria-hidden="true">#</a> SQL TOP 实例</h3><p>表中选取头两条记录:</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token keyword">TOP</span> <span class="token number">2</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> Persons
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h3 id="sql-top-percent-实例" tabindex="-1"><a class="header-anchor" href="#sql-top-percent-实例" aria-hidden="true">#</a> SQL TOP PERCENT 实例</h3><p>表中选取 50% 的记录:</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token keyword">TOP</span> <span class="token number">50</span> <span class="token keyword">PERCENT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> Persons
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h2 id="sql-like-操作符" tabindex="-1"><a class="header-anchor" href="#sql-like-操作符" aria-hidden="true">#</a> SQL LIKE 操作符</h2><blockquote><p>LIKE 操作符用于在WHERE 子句中搜索列中的指定模式</p></blockquote><h3 id="sql-like-操作符语法" tabindex="-1"><a class="header-anchor" href="#sql-like-操作符语法" aria-hidden="true">#</a> SQL LIKE 操作符语法：</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> column_name<span class="token punctuation">(</span>s<span class="token punctuation">)</span> <span class="token keyword">FROM</span> table_name <span class="token keyword">WHERE</span> column_name <span class="token operator">LIKE</span> pattern
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h3 id="like-操作符实例" tabindex="-1"><a class="header-anchor" href="#like-操作符实例" aria-hidden="true">#</a> LIKE 操作符实例</h3><p>例子1</p><p>从 &quot;Persons&quot; 表中选取居住在以 &quot;N&quot; 开始的城市里的人：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> Persons <span class="token keyword">WHERE</span> City <span class="token operator">LIKE</span> <span class="token string">&#39;N%&#39;</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><div class="custom-container tip"><p class="custom-container-title">TIP</p><p>&quot;%&quot;可用于定义通配符(模式中缺少的字母)</p></div><p>例子2</p><p>从 &quot;Persons&quot; 表中选取居住在以 &quot;g&quot; 结尾的城市里的人：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> Persons <span class="token keyword">WHERE</span> City <span class="token operator">LIKE</span> <span class="token string">&#39;%g&#39;</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>例子3</p><p>从 &quot;Persons&quot; 表中选取居住在包含 &quot;lon&quot; 的城市里的人：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> Persons <span class="token keyword">WHERE</span> City <span class="token operator">LIKE</span> <span class="token string">&#39;%lon%&#39;</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>例子4</p><p>通过使用 NOT 关键字，我们可以从 &quot;Persons&quot; 表中选取居住在<em>不包含</em> &quot;lon&quot; 的城市里的人：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> Persons <span class="token keyword">WHERE</span> City <span class="token operator">NOT</span> <span class="token operator">LIKE</span> <span class="token string">&#39;%lon%&#39;</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h2 id="sql-通配符" tabindex="-1"><a class="header-anchor" href="#sql-通配符" aria-hidden="true">#</a> SQL 通配符</h2><blockquote><p>在搜索数据中的数据时，SQL通配符可以替代一个或多个字符</p></blockquote><table><thead><tr><th>通配符</th><th>描述</th></tr></thead><tbody><tr><td>%</td><td>代表零个或多个字符</td></tr><tr><td>_</td><td>仅替代一个字符</td></tr><tr><td>[]</td><td>字符列中的任何单一字符</td></tr><tr><td>[^]</td><td>不再字符列中的任何单一字符</td></tr></tbody></table><h3 id="使用-通配符" tabindex="-1"><a class="header-anchor" href="#使用-通配符" aria-hidden="true">#</a> 使用 <code>%</code> 通配符</h3><p>例子1</p><p>从 &quot;Persons&quot; 表中选取居住在以 &quot;Ne&quot; 开始的城市里的人：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> Persons <span class="token keyword">WHERE</span> City <span class="token operator">LIKE</span> <span class="token string">&#39;Ne%&#39;</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>例子2</p><p>从 &quot;Persons&quot; 表中选取居住在包含 &quot;lond&quot; 的城市里的人：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> Persons <span class="token keyword">WHERE</span> City <span class="token operator">LIKE</span> <span class="token string">&#39;%lond%&#39;</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h3 id="使用-通配符-1" tabindex="-1"><a class="header-anchor" href="#使用-通配符-1" aria-hidden="true">#</a> 使用 <code>_</code> 通配符</h3><p>例子1</p><p>从 &quot;Persons&quot; 表中选取名字的第一个字符之后是 &quot;eorge&quot; 的人：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> Persons <span class="token keyword">WHERE</span> FirstName <span class="token operator">LIKE</span> <span class="token string">&#39;_enorge&#39;</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>例子2</p><p>从 &quot;Persons&quot; 表中选取的这条记录的姓氏以 &quot;C&quot; 开头，然后是一个任意字符，然后是 &quot;r&quot;，然后是一个任意字符，然后是 &quot;er&quot;：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> Persons <span class="token keyword">WHERE</span> LastName <span class="token operator">LIKE</span> <span class="token string">&#39;C_r_er&#39;</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h3 id="使用-通配符-2" tabindex="-1"><a class="header-anchor" href="#使用-通配符-2" aria-hidden="true">#</a> 使用 <code>[]</code> 通配符</h3><p>例子1</p><p>从 &quot;Persons&quot; 表中选取居住的城市以 &quot;A&quot; 或 &quot;L&quot; 或 &quot;N&quot; 开头的人：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> Persons <span class="token keyword">WHERE</span> City <span class="token operator">LIKE</span> <span class="token string">&#39;[ALN]%&#39;</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>例子2</p><p>从 &quot;Persons&quot; 表中选取居住的城市<em>不以</em> &quot;A&quot; 或 &quot;L&quot; 或 &quot;N&quot; 开头的人：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> Persons <span class="token keyword">WHERE</span> City <span class="token operator">LIKE</span> <span class="token string">&#39;[!ALN]%&#39;</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h2 id="sql-in-操作符" tabindex="-1"><a class="header-anchor" href="#sql-in-操作符" aria-hidden="true">#</a> SQL IN 操作符</h2><blockquote><p>IN 操作符允许我们在 WHERE 子句中规定多个值</p></blockquote><h3 id="sql-in-语法" tabindex="-1"><a class="header-anchor" href="#sql-in-语法" aria-hidden="true">#</a> SQL IN 语法</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> column_name<span class="token punctuation">(</span>s<span class="token punctuation">)</span> <span class="token keyword">FROm</span> table_name <span class="token keyword">WHERE</span> column_name <span class="token operator">IN</span> <span class="token punctuation">(</span>value1<span class="token punctuation">,</span>value2<span class="token punctuation">,</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h3 id="in-操作符实例" tabindex="-1"><a class="header-anchor" href="#in-操作符实例" aria-hidden="true">#</a> IN 操作符实例</h3><p>们希望从&quot;Persons&quot;表中选取姓氏为 Adams 和 Carter 的人：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> Persons <span class="token keyword">WHERE</span> LastName <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token string">&#39;Adams&#39;</span><span class="token punctuation">,</span><span class="token string">&#39;Carter&#39;</span><span class="token punctuation">)</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h2 id="sql-between-操作符" tabindex="-1"><a class="header-anchor" href="#sql-between-操作符" aria-hidden="true">#</a> SQL BETWEEN 操作符</h2><blockquote><p>操作符 BETWEEN...AND 会选取两个值之间的数据范围。这些值可以时数值、文本或者日期</p></blockquote><h3 id="sql-between-语法" tabindex="-1"><a class="header-anchor" href="#sql-between-语法" aria-hidden="true">#</a> SQL BETWEEN 语法</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> column_name<span class="token punctuation">(</span>s<span class="token punctuation">)</span> <span class="token keyword">FROM</span> table_name <span class="token keyword">WHERE</span> column_name <span class="token operator">BETWEEN</span> <span class="token keyword">value</span> <span class="token operator">AND</span> value2
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>例子1</p><p>以最后名字顺序显示介于 &quot;Adams&quot;（包括）和 &quot;Carter&quot;（不包括）之间的人，请使用下面的 SQL：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> Persons <span class="token keyword">WHERE</span> LastName <span class="token operator">BETWEEN</span> <span class="token string">&#39;Adams&#39;</span> <span class="token operator">AND</span> <span class="token string">&#39;Carter&#39;</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>例子2</p><p>如需使用上面的例子显示范围之外的人，请使用 NOT 操作符：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> Persons <span class="token keyword">WHERE</span> LastName <span class="token operator">NOT</span> <span class="token operator">BETWEEN</span> <span class="token string">&#39;Adams&#39;</span> <span class="token operator">AND</span> <span class="token string">&#39;Carter&#39;</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h2 id="sql-alias" tabindex="-1"><a class="header-anchor" href="#sql-alias" aria-hidden="true">#</a> SQL Alias</h2><blockquote><p>通过使用SQL，可以为列名称和表名称指定别名</p></blockquote><h3 id="sql-alias-1" tabindex="-1"><a class="header-anchor" href="#sql-alias-1" aria-hidden="true">#</a> SQL Alias</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> column_name<span class="token punctuation">(</span>s<span class="token punctuation">)</span>  <span class="token keyword">FROM</span> table_name1 <span class="token keyword">AS</span> alias_name 
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h3 id="列的sql-alias-语法" tabindex="-1"><a class="header-anchor" href="#列的sql-alias-语法" aria-hidden="true">#</a> 列的SQL Alias 语法</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> column_name <span class="token keyword">AS</span> alias_name <span class="token keyword">FROM</span> table_name
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h3 id="alias-实例-使用表明称别名" tabindex="-1"><a class="header-anchor" href="#alias-实例-使用表明称别名" aria-hidden="true">#</a> Alias 实例：使用表明称别名</h3><p>假设我们有两个表分别是：&quot;Persons&quot; 和 &quot;Product_Orders&quot;。我们分别为它们指定别名 &quot;p&quot; 和 &quot;po&quot;。现在，我们希望列出尾字为 &quot;John&quot;,首字为&quot; Adams&quot; 的所有定单。</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> po<span class="token punctuation">.</span>OrderID<span class="token punctuation">,</span>p<span class="token punctuation">.</span>LastName<span class="token punctuation">,</span>p<span class="token punctuation">.</span>FirstName <span class="token keyword">FROM</span> Persons <span class="token keyword">AS</span> p<span class="token punctuation">,</span>Product_Orders <span class="token keyword">AS</span> po <span class="token keyword">WHERE</span> p<span class="token punctuation">.</span>LastName<span class="token operator">=</span><span class="token string">&#39;Adams&#39;</span> <span class="token operator">AND</span> p<span class="token punctuation">.</span>FirstName<span class="token operator">=</span><span class="token string">&#39;John&#39;</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h2 id="sql-join" tabindex="-1"><a class="header-anchor" href="#sql-join" aria-hidden="true">#</a> SQL JOIN</h2><blockquote><p>SQL join 用于更具两个或多个表中的列之间的关系，从这些表中查询数据。</p></blockquote><h3 id="join和key" tabindex="-1"><a class="header-anchor" href="#join和key" aria-hidden="true">#</a> Join和Key</h3><blockquote><p>有时为了得到完整的结果，我们需要从两个或更多的表中获取结果，我们就需要执行 join</p><p>数据库中的表可以通过键将彼此联系起来，主键是一个列，在这个列中的每一行的值都是唯一的，这样做的目的是在不重复的每个表中的所有数据的情况下，把表间的数据交叉捆绑在一起。</p></blockquote><h3 id="引用两个表" tabindex="-1"><a class="header-anchor" href="#引用两个表" aria-hidden="true">#</a> 引用两个表</h3><p>我们可以通过引用两个表的方式，从两个表中获取数据：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> Persons<span class="token punctuation">.</span>LastName<span class="token punctuation">,</span>Persons<span class="token punctuation">.</span>FirstName<span class="token punctuation">,</span>Orders<span class="token punctuation">.</span>OrderNo <span class="token keyword">FROM</span> Persons<span class="token punctuation">,</span>Orders <span class="token keyword">WHERE</span> Persons<span class="token punctuation">.</span>Id_P <span class="token operator">=</span> Orders<span class="token punctuation">.</span>Id_P
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h3 id="sql-join-使用join" tabindex="-1"><a class="header-anchor" href="#sql-join-使用join" aria-hidden="true">#</a> SQL JOIN - 使用Join</h3><p>除了上面的方法，我们也可以使用关键词JOIN来从两个表中获取数据</p><p>如果我们希望列出所有人的定购，可以使用下面的 SELECT 语句：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> Persons<span class="token punctuation">.</span>LastName<span class="token punctuation">,</span>Persons<span class="token punctuation">.</span>FirstName<span class="token punctuation">,</span>Orders<span class="token punctuation">.</span>OrderNo <span class="token keyword">FROM</span> Persons <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> Orders <span class="token keyword">ON</span> Persons<span class="token punctuation">.</span>Id_P <span class="token operator">=</span> Orders<span class="token punctuation">.</span>Id_P <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> Persons<span class="token punctuation">.</span>LastName
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h3 id="不同的-sql-join" tabindex="-1"><a class="header-anchor" href="#不同的-sql-join" aria-hidden="true">#</a> 不同的 SQL JOIN</h3><ul><li>JOIN ： 如果表中有至少一个匹配，则返回行</li><li>LEFT JOIN 即使右表中没有匹配，也从左表返回所有的行</li><li>RIGHT JOIN 即使左表中没有匹配，也从右表返回所有的行</li><li>FULL JOIN 只要其中一个表中存在匹配，就返回行</li></ul><h2 id="sql-inner-join-关键字" tabindex="-1"><a class="header-anchor" href="#sql-inner-join-关键字" aria-hidden="true">#</a> SQL INNER JOIN 关键字</h2><blockquote><p>在表中存在至少一个匹配时，INNER JOIN 关键字返回行</p></blockquote><p>&quot;Persons&quot;表</p><table><thead><tr><th>Id_P</th><th>LastName</th><th>FirstName</th><th>Address</th><th>City</th></tr></thead><tbody><tr><td>1</td><td>Adams</td><td>John</td><td>Oxford Street</td><td>London</td></tr><tr><td>2</td><td>Bush</td><td>George</td><td>Fifth Avenue</td><td>New York</td></tr><tr><td>3</td><td>Carter</td><td>Thomas</td><td>Changan Street</td><td>Beijing</td></tr></tbody></table><p>&quot;Orders&quot;表</p><table><thead><tr><th>Id_O</th><th>OrderNo</th></tr></thead><tbody><tr><td>1</td><td>77895</td></tr><tr><td>2</td><td>44678</td></tr><tr><td>3</td><td>22456</td></tr><tr><td>4</td><td>24562</td></tr><tr><td>5</td><td>34764</td></tr></tbody></table><h3 id="inner-join-关键字语法" tabindex="-1"><a class="header-anchor" href="#inner-join-关键字语法" aria-hidden="true">#</a> INNER JOIN 关键字语法</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token keyword">column</span><span class="token punctuation">)</span>name<span class="token punctuation">(</span>s<span class="token punctuation">)</span> <span class="token keyword">FROM</span> table_name1 <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> table_name2 <span class="token keyword">ON</span> table_name1<span class="token punctuation">.</span>column_name<span class="token operator">=</span>table_name2<span class="token punctuation">.</span>column_name
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p><code>INNER JOIN 与 JOIN是相同的</code></p><h3 id="内连接-inner-join-实例" tabindex="-1"><a class="header-anchor" href="#内连接-inner-join-实例" aria-hidden="true">#</a> 内连接（INNER JOIN）实例</h3><p>现在，我们希望列出所有人的定购。您可以使用下面的 SELECT 语句：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> Persons<span class="token punctuation">.</span>LastName<span class="token punctuation">,</span>Persons<span class="token punctuation">.</span>FirstName<span class="token punctuation">,</span>Orders<span class="token punctuation">.</span>OrderNo <span class="token keyword">FROM</span> Persons <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> Orders <span class="token keyword">ON</span> Persons<span class="token punctuation">.</span>Id_P <span class="token operator">=</span> Orders<span class="token punctuation">.</span>Id_p <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> Persons<span class="token punctuation">.</span>LastName
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>结果集：</p><table><thead><tr><th>LastName</th><th>FirstName</th><th>OrderNo</th></tr></thead><tbody><tr><td>Adams</td><td>John</td><td>22456</td></tr><tr><td>Adams</td><td>John</td><td>24562</td></tr><tr><td>Carter</td><td>Thomas</td><td>77895</td></tr></tbody></table><p>INNER JOIN 关键字在表中存在至少一个匹配时返回行。如果&quot;Persons&quot;中的行在&quot;Orders&quot;中没有匹配，就不会列出这些行。</p><h2 id="sql-left-join" tabindex="-1"><a class="header-anchor" href="#sql-left-join" aria-hidden="true">#</a> SQL LEFT JOIN</h2><blockquote><p>LEFT JOIN 关键字会从左表（table_name1）那里返回所有的行，即使在右表（table_name2）中没有匹配的行。</p></blockquote><h3 id="left-join-关键字" tabindex="-1"><a class="header-anchor" href="#left-join-关键字" aria-hidden="true">#</a> LEFT JOIN 关键字</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> column_name<span class="token punctuation">(</span>s<span class="token punctuation">)</span> <span class="token keyword">FROM</span> table_name1
<span class="token keyword">FROM</span> table_name1
<span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> table_name2
<span class="token keyword">ON</span> table_name1<span class="token punctuation">.</span>column_name<span class="token operator">=</span>table_name2<span class="token punctuation">.</span>column_name
</code></pre><div class="line-numbers"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>注释：在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN</p><p>&quot;Persons&quot;表</p><table><thead><tr><th>Id_P</th><th>LastName</th><th>FirstName</th><th>Address</th><th>City</th></tr></thead><tbody><tr><td>1</td><td>Adams</td><td>John</td><td>Oxford Street</td><td>London</td></tr><tr><td>2</td><td>Bush</td><td>George</td><td>Fifth Avenue</td><td>New York</td></tr><tr><td>3</td><td>Carter</td><td>Thomas</td><td>Changan Street</td><td>Beijing</td></tr></tbody></table><p>&quot;Orders&quot;表</p><table><thead><tr><th>Id_O</th><th>OrderNo</th><th>Id_P</th></tr></thead><tbody><tr><td>1</td><td>77895</td><td>3</td></tr><tr><td>2</td><td>44678</td><td>3</td></tr><tr><td>3</td><td>22456</td><td>1</td></tr><tr><td>4</td><td>24562</td><td>1</td></tr><tr><td>5</td><td>34764</td><td>65</td></tr></tbody></table><h3 id="左连接-left-join-实例" tabindex="-1"><a class="header-anchor" href="#左连接-left-join-实例" aria-hidden="true">#</a> 左连接(LEFT JOIN)实例</h3><p>现在，我们希望列出所有的人，以及他们的定购 - 如果有的话。</p><p>您可以使用下面的 SELECT 语句：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> Persons<span class="token punctuation">.</span>LastName<span class="token punctuation">,</span>Persons<span class="token punctuation">.</span>FirstName<span class="token punctuation">,</span>Orders<span class="token punctuation">.</span>OrderNo
<span class="token keyword">FROM</span> Persons
<span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> Orders
<span class="token keyword">ON</span> Persons<span class="token punctuation">.</span>Id_P <span class="token operator">=</span> Orders<span class="token punctuation">.</span>Id_P
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> Persons<span class="token punctuation">.</span>LastName
</code></pre><div class="line-numbers"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p>结果集：</p><table><thead><tr><th>LastName</th><th>FirstName</th><th>OrderNo</th></tr></thead><tbody><tr><td>Adams</td><td>John</td><td>22456</td></tr><tr><td>Adams</td><td>John</td><td>24562</td></tr><tr><td>Carter</td><td>Thomas</td><td>77895</td></tr><tr><td>Carter</td><td>Thomas</td><td>44678</td></tr><tr><td>Bush</td><td>George</td><td></td></tr></tbody></table><p>LEFT JOIN 关键字会从左表（Persons）那里返回所有的行，即使在右表（Orders）中没有匹配的行</p><h2 id="sql-right-join-关键字" tabindex="-1"><a class="header-anchor" href="#sql-right-join-关键字" aria-hidden="true">#</a> SQL RIGHT JOIN 关键字</h2><blockquote><p>RIGHT JOIN 关键字会右表（table_name2）那里返回所有的行，即使在左表（table_name1）中没有匹配的行</p></blockquote><h3 id="right-join-关键字语法" tabindex="-1"><a class="header-anchor" href="#right-join-关键字语法" aria-hidden="true">#</a> RIGHT JOIN 关键字语法</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> column_name<span class="token punctuation">(</span>s<span class="token punctuation">)</span>
<span class="token keyword">FROM</span> table_name1
<span class="token keyword">RIGHT</span> <span class="token keyword">JOIN</span> table_name2
<span class="token keyword">ON</span> table_name1<span class="token punctuation">.</span>column_name<span class="token operator">=</span>table_name2<span class="token punctuation">.</span>column_name
</code></pre><div class="line-numbers"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>注释：在某些数据中，RIGHT JON 称为 RIGHT OUTER JOIN</p><p>&quot;Persons&quot;表</p><table><thead><tr><th>Id_P</th><th>LastName</th><th>FirstName</th><th>Address</th><th>City</th></tr></thead><tbody><tr><td>1</td><td>Adams</td><td>John</td><td>Oxford Street</td><td>London</td></tr><tr><td>2</td><td>Bush</td><td>George</td><td>Fifth Avenue</td><td>New York</td></tr><tr><td>3</td><td>Carter</td><td>Thomas</td><td>Changan Street</td><td>Beijing</td></tr></tbody></table><p>&quot;Orders&quot;表</p><table><thead><tr><th>Id_O</th><th>OrderNo</th><th>Id_P</th></tr></thead><tbody><tr><td>1</td><td>77895</td><td>3</td></tr><tr><td>2</td><td>44678</td><td>3</td></tr><tr><td>3</td><td>22456</td><td>1</td></tr><tr><td>4</td><td>24562</td><td>1</td></tr><tr><td>5</td><td>34764</td><td>65</td></tr></tbody></table><h3 id="右连接-right-join-实例" tabindex="-1"><a class="header-anchor" href="#右连接-right-join-实例" aria-hidden="true">#</a> 右连接（RIGHT JOIN）实例</h3><p>现在，我们希望列出所有的定单，以及定购它们的人 - 如果有的话。</p><p>您可以使用下面的 SELECT 语句：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> Persons<span class="token punctuation">.</span>LastName<span class="token punctuation">,</span>Persons<span class="token punctuation">.</span>FirstName<span class="token punctuation">,</span>Orders<span class="token punctuation">.</span>OrderNo
<span class="token keyword">FROM</span> Persons
<span class="token keyword">RIGHT</span> <span class="token keyword">JOIN</span> Orders
<span class="token keyword">ON</span> Persons<span class="token punctuation">.</span>Id_P <span class="token operator">=</span> Orders<span class="token punctuation">.</span>Id_p
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> Persons<span class="token punctuation">.</span>LastName
</code></pre><div class="line-numbers"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p>结构集：</p><table><thead><tr><th>LastName</th><th>FirstName</th><th>OrderNo</th></tr></thead><tbody><tr><td>Adams</td><td>John</td><td>22456</td></tr><tr><td>Adams</td><td>John</td><td>24562</td></tr><tr><td>Carter</td><td>Thomas</td><td>77895</td></tr><tr><td>Carter</td><td>Thomas</td><td>44678</td></tr><tr><td></td><td></td><td>34764</td></tr></tbody></table><p>RIGHT JOIN 关键字会从右表（Orders）那里返回所有的行，即使在左表（Persons）中没有匹配的行</p><h2 id="sql-full-join-关键字" tabindex="-1"><a class="header-anchor" href="#sql-full-join-关键字" aria-hidden="true">#</a> SQL FULL JOIN 关键字</h2><blockquote><p>只要其中某个表存在匹配。FULL JOIN 关键字就会返回行</p></blockquote><h3 id="full-join-关键字" tabindex="-1"><a class="header-anchor" href="#full-join-关键字" aria-hidden="true">#</a> FULL JOIN 关键字</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> column_name<span class="token punctuation">(</span>s<span class="token punctuation">)</span>
<span class="token keyword">FROM</span> table_name1
<span class="token keyword">FULL</span> <span class="token keyword">JOIN</span> table_name2
<span class="token keyword">ON</span> table_name1<span class="token punctuation">.</span>column_name <span class="token operator">=</span> table_name2<span class="token punctuation">.</span>column_name
</code></pre><div class="line-numbers"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>注释：在某些数据中，FULL JOIN 称为 FULL OUTER JOIN</p><p>&quot;Persons&quot;表</p><table><thead><tr><th>Id_P</th><th>LastName</th><th>FirstName</th><th>Address</th><th>City</th></tr></thead><tbody><tr><td>1</td><td>Adams</td><td>John</td><td>Oxford Street</td><td>London</td></tr><tr><td>2</td><td>Bush</td><td>George</td><td>Fifth Avenue</td><td>New York</td></tr><tr><td>3</td><td>Carter</td><td>Thomas</td><td>Changan Street</td><td>Beijing</td></tr></tbody></table><p>&quot;Orders&quot;表</p><table><thead><tr><th>Id_O</th><th>OrderNo</th><th>Id_P</th></tr></thead><tbody><tr><td>1</td><td>77895</td><td>3</td></tr><tr><td>2</td><td>44678</td><td>3</td></tr><tr><td>3</td><td>22456</td><td>1</td></tr><tr><td>4</td><td>24562</td><td>1</td></tr><tr><td>5</td><td>34764</td><td>65</td></tr></tbody></table><h3 id="全连接-full-join-实例" tabindex="-1"><a class="header-anchor" href="#全连接-full-join-实例" aria-hidden="true">#</a> 全连接（FULL JOIN）实例</h3><p>现在，我们希望列出所有的人，以及他们的定单，以及所有的定单，以及定购它们的人。</p><p>您可以使用下面的 SELECT 语句：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> Persons<span class="token punctuation">.</span>LastName<span class="token punctuation">,</span>Persons<span class="token punctuation">.</span>FirstName<span class="token punctuation">,</span>Orders<span class="token punctuation">.</span>OrderNo
<span class="token keyword">FROM</span> Persons
<span class="token keyword">FULL</span> <span class="token keyword">JOIN</span> Orders
<span class="token keyword">ON</span> Persons<span class="token punctuation">.</span>Id_P <span class="token operator">=</span> Orders<span class="token punctuation">.</span>Id_P
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> Persons<span class="token punctuation">.</span>LastName
</code></pre><div class="line-numbers"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p>结果集：</p><table><thead><tr><th>LastName</th><th>FirstName</th><th>OrderNo</th></tr></thead><tbody><tr><td>Adams</td><td>John</td><td>22456</td></tr><tr><td>Adams</td><td>John</td><td>24562</td></tr><tr><td>Carter</td><td>Thomas</td><td>77895</td></tr><tr><td>Carter</td><td>Thomas</td><td>44678</td></tr><tr><td>Bush</td><td>George</td><td></td></tr><tr><td></td><td></td><td>34764</td></tr></tbody></table><p>FULL JOIN 关键字会从表（Persons）和右表（Orders）那里返回所有的行。如果“Persons”中的行在表“Orders”中没有匹配，或着如果“Orders”中的行在表“Persons”中的行在表“Persons”中没有匹配，这些行同样会列出。</p><h2 id="sql-union-和-union-all-操作符" tabindex="-1"><a class="header-anchor" href="#sql-union-和-union-all-操作符" aria-hidden="true">#</a> SQL UNION 和 UNION ALL 操作符</h2><blockquote><p>UNION 操作符用于合并两个或多个 SELECT 语句的结果集</p><p>请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。</p></blockquote><h3 id="sql-union-语法" tabindex="-1"><a class="header-anchor" href="#sql-union-语法" aria-hidden="true">#</a> SQL UNION 语法</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> column_name<span class="token punctuation">(</span>s<span class="token punctuation">)</span> <span class="token keyword">FROM</span> table_name1
<span class="token keyword">UNION</span>
<span class="token keyword">SELECT</span> column_name<span class="token punctuation">(</span>s<span class="token punctuation">)</span> <span class="token keyword">FROM</span> table_name2
</code></pre><div class="line-numbers"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p>注释：默认的。UNION 操作符选取不同的值，如果允许重复的值，请使用UNION ALL</p><h3 id="sql-union-all-语法" tabindex="-1"><a class="header-anchor" href="#sql-union-all-语法" aria-hidden="true">#</a> SQL UNION ALL 语法</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> column_name<span class="token punctuation">(</span>s<span class="token punctuation">)</span> <span class="token keyword">FROM</span> table_name1
<span class="token keyword">UNION</span> <span class="token keyword">ALL</span>
<span class="token keyword">SELECT</span> column_name<span class="token punctuation">(</span>s<span class="token punctuation">)</span> <span class="token keyword">FROM</span> table_name2
</code></pre><div class="line-numbers"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h2 id="sql-select-into-语句" tabindex="-1"><a class="header-anchor" href="#sql-select-into-语句" aria-hidden="true">#</a> SQL SELECT INTO 语句</h2><blockquote><p>SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中。</p><p>SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。SELECT INTO</p></blockquote><h3 id="sql-select-into-语句-1" tabindex="-1"><a class="header-anchor" href="#sql-select-into-语句-1" aria-hidden="true">#</a> SQL SELECT INTO 语句</h3><p><code>可以把所有的列插入新表</code>：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">INTO</span> new_table_name <span class="token punctuation">[</span><span class="token operator">IN</span> externaldatabase<span class="token punctuation">]</span>
<span class="token keyword">FROM</span> old_tablename
</code></pre><div class="line-numbers"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p><code>或者只吧希望的列插入新表</code>：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> column_name<span class="token punctuation">(</span>s<span class="token punctuation">)</span> <span class="token keyword">INTO</span> new_table_name <span class="token punctuation">[</span><span class="token operator">IN</span> externaldatabase<span class="token punctuation">]</span>
<span class="token keyword">FROM</span> old_tablename
</code></pre><div class="line-numbers"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><h2 id="sql-avg-函数" tabindex="-1"><a class="header-anchor" href="#sql-avg-函数" aria-hidden="true">#</a> SQL AVG 函数</h2><blockquote><p>AVG函数返回数值列出的平均值。NILL 值不包括在计算中</p></blockquote><h3 id="sql-avg-语法" tabindex="-1"><a class="header-anchor" href="#sql-avg-语法" aria-hidden="true">#</a> SQL AVG() 语法</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">AVG</span><span class="token punctuation">(</span>column_name<span class="token punctuation">)</span> <span class="token keyword">FROM</span> table_name
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h3 id="sql-avg-实例" tabindex="-1"><a class="header-anchor" href="#sql-avg-实例" aria-hidden="true">#</a> SQL AVG() 实例</h3><p>我们拥有下面这个 &quot;Orders&quot; 表：</p><table><thead><tr><th style="text-align:left;">O_Id</th><th style="text-align:left;">OrderDate</th><th style="text-align:left;">OrderPrice</th><th style="text-align:left;">Customer</th></tr></thead><tbody><tr><td style="text-align:left;">1</td><td style="text-align:left;">2008/12/29</td><td style="text-align:left;">1000</td><td style="text-align:left;">Bush</td></tr><tr><td style="text-align:left;">2</td><td style="text-align:left;">2008/11/23</td><td style="text-align:left;">1600</td><td style="text-align:left;">Carter</td></tr><tr><td style="text-align:left;">3</td><td style="text-align:left;">2008/10/05</td><td style="text-align:left;">700</td><td style="text-align:left;">Bush</td></tr><tr><td style="text-align:left;">4</td><td style="text-align:left;">2008/09/28</td><td style="text-align:left;">300</td><td style="text-align:left;">Bush</td></tr><tr><td style="text-align:left;">5</td><td style="text-align:left;">2008/08/06</td><td style="text-align:left;">2000</td><td style="text-align:left;">Adams</td></tr><tr><td style="text-align:left;">6</td><td style="text-align:left;">2008/07/21</td><td style="text-align:left;">100</td><td style="text-align:left;">Carter</td></tr></tbody></table><p>例子1:</p><p>现在，我们希望计算 &quot;OrderPrice&quot; 字段的平均值。</p><p>我们使用如下 SQL 语句：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">AVG</span><span class="token punctuation">(</span>OrderPrice<span class="token punctuation">)</span> <span class="token keyword">AS</span> OrderAverage <span class="token keyword">FROM</span> Orders
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>结果集：</p><table><thead><tr><th style="text-align:left;">OrderAverage</th></tr></thead><tbody><tr><td style="text-align:left;">950</td></tr></tbody></table><p>例子2:</p><p>现在，我们希望找到 OrderPrice 值高于 OrderPrice 平均值的客户。</p><p>我们使用如下 SQL 语句：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> Customer <span class="token keyword">FROM</span> Orders <span class="token keyword">WHERE</span> OrderPrice<span class="token operator">&gt;</span><span class="token punctuation">(</span><span class="token keyword">SElECT</span> <span class="token function">AVG</span><span class="token punctuation">(</span>OrderPrice<span class="token punctuation">)</span> <span class="token keyword">FROM</span> Orders<span class="token punctuation">)</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>结果集：</p><table><thead><tr><th style="text-align:left;">Customer</th></tr></thead><tbody><tr><td style="text-align:left;">Bush</td></tr><tr><td style="text-align:left;">Carter</td></tr><tr><td style="text-align:left;">Adams</td></tr></tbody></table><h2 id="sql-count-函数" tabindex="-1"><a class="header-anchor" href="#sql-count-函数" aria-hidden="true">#</a> SQL COUNT() 函数</h2><blockquote><p>COUNT() 函数返回匹配指定条件的行树</p></blockquote><h3 id="sql-count-语法" tabindex="-1"><a class="header-anchor" href="#sql-count-语法" aria-hidden="true">#</a> SQL COUNT() 语法</h3><p><code>SQL_COUNT(column_name)语法</code></p><p>COUNT(column_name)函数返回指定列的值的数目(NULL不计入)</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span>column_name<span class="token punctuation">)</span> <span class="token keyword">FROM</span> table_name
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h3 id="sql-count-语法-1" tabindex="-1"><a class="header-anchor" href="#sql-count-语法-1" aria-hidden="true">#</a> SQL COUNT(*) 语法</h3><p>COUNT(*) 函数返回表中的记录数：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> table_name
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h3 id="sql-count-distinct-column-name-语法" tabindex="-1"><a class="header-anchor" href="#sql-count-distinct-column-name-语法" aria-hidden="true">#</a> SQL COUNT(DISTINCT column_name)语法</h3><p>COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token keyword">DISTINCT</span> column_name<span class="token punctuation">)</span> <span class="token keyword">FROM</span> table_name
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h3 id="sql-count-column-name-实例" tabindex="-1"><a class="header-anchor" href="#sql-count-column-name-实例" aria-hidden="true">#</a> SQL COUNT(column_name)实例</h3><p>我们拥有下列 &quot;Orders&quot; 表：</p><table><thead><tr><th style="text-align:left;">O_Id</th><th style="text-align:left;">OrderDate</th><th style="text-align:left;">OrderPrice</th><th style="text-align:left;">Customer</th></tr></thead><tbody><tr><td style="text-align:left;">1</td><td style="text-align:left;">2008/12/29</td><td style="text-align:left;">1000</td><td style="text-align:left;">Bush</td></tr><tr><td style="text-align:left;">2</td><td style="text-align:left;">2008/11/23</td><td style="text-align:left;">1600</td><td style="text-align:left;">Carter</td></tr><tr><td style="text-align:left;">3</td><td style="text-align:left;">2008/10/05</td><td style="text-align:left;">700</td><td style="text-align:left;">Bush</td></tr><tr><td style="text-align:left;">4</td><td style="text-align:left;">2008/09/28</td><td style="text-align:left;">300</td><td style="text-align:left;">Bush</td></tr><tr><td style="text-align:left;">5</td><td style="text-align:left;">2008/08/06</td><td style="text-align:left;">2000</td><td style="text-align:left;">Adams</td></tr><tr><td style="text-align:left;">6</td><td style="text-align:left;">2008/07/21</td><td style="text-align:left;">100</td><td style="text-align:left;">Carter</td></tr></tbody></table><p>现在，我们希望计算客户 &quot;Carter&quot; 的订单数。</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span>Customer<span class="token punctuation">)</span> <span class="token keyword">AS</span> CustomerNilsen <span class="token keyword">FROM</span> Orders <span class="token keyword">WHERE</span> Customer <span class="token operator">=</span> <span class="token string">&#39;Carter&#39;</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>结果集：</p><table><thead><tr><th style="text-align:left;">CustomerNilsen</th></tr></thead><tbody><tr><td style="text-align:left;">2</td></tr></tbody></table><h3 id="sql-count-实例" tabindex="-1"><a class="header-anchor" href="#sql-count-实例" aria-hidden="true">#</a> SQL COUNT(*) 实例</h3><p>如果我们省略 WHERE 子句，比如这样：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> NumberOfOrders <span class="token keyword">FROM</span> Orders
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>结果集：</p><table><thead><tr><th style="text-align:left;">NumberOfOrders</th></tr></thead><tbody><tr><td style="text-align:left;">6</td></tr></tbody></table><h3 id="sql-count-distinct-column-name-实例" tabindex="-1"><a class="header-anchor" href="#sql-count-distinct-column-name-实例" aria-hidden="true">#</a> SQL COUNT(DISTINCT column_name) 实例</h3><p>现在，我们希望计算 &quot;Orders&quot; 表中不同客户的数目。</p><p>我们使用如下 SQL 语句：</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token keyword">DISTINCT</span> Customer<span class="token punctuation">)</span> <span class="token keyword">AS</span> NumberOfCustomers <span class="token keyword">FROM</span> Orders
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>结果集：</p><table><thead><tr><th style="text-align:left;">NumberOfOrders</th></tr></thead><tbody><tr><td style="text-align:left;">6</td></tr></tbody></table><h2 id="sql-first-函数" tabindex="-1"><a class="header-anchor" href="#sql-first-函数" aria-hidden="true">#</a> SQL FIRST() 函数</h2><blockquote><p>FIRST()函数返回指定的字段中第一个记录的值</p><p>tips：可使用ORDER BY 语句记录进行排序</p></blockquote><h3 id="sql-first-语法" tabindex="-1"><a class="header-anchor" href="#sql-first-语法" aria-hidden="true">#</a> SQL FIRST() 语法</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">FIRST</span><span class="token punctuation">(</span>column_name<span class="token punctuation">)</span> <span class="token keyword">FROM</span> table_name
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h3 id="sql-first-实例" tabindex="-1"><a class="header-anchor" href="#sql-first-实例" aria-hidden="true">#</a> SQL FIRST() 实例</h3><p>我们拥有下面这个 &quot;Orders&quot; 表：</p><table><thead><tr><th style="text-align:left;">O_Id</th><th style="text-align:left;">OrderDate</th><th style="text-align:left;">OrderPrice</th><th style="text-align:left;">Customer</th></tr></thead><tbody><tr><td style="text-align:left;">1</td><td style="text-align:left;">2008/12/29</td><td style="text-align:left;">1000</td><td style="text-align:left;">Bush</td></tr><tr><td style="text-align:left;">2</td><td style="text-align:left;">2008/11/23</td><td style="text-align:left;">1600</td><td style="text-align:left;">Carter</td></tr><tr><td style="text-align:left;">3</td><td style="text-align:left;">2008/10/05</td><td style="text-align:left;">700</td><td style="text-align:left;">Bush</td></tr><tr><td style="text-align:left;">4</td><td style="text-align:left;">2008/09/28</td><td style="text-align:left;">300</td><td style="text-align:left;">Bush</td></tr><tr><td style="text-align:left;">5</td><td style="text-align:left;">2008/08/06</td><td style="text-align:left;">2000</td><td style="text-align:left;">Adams</td></tr><tr><td style="text-align:left;">6</td><td style="text-align:left;">2008/07/21</td><td style="text-align:left;">100</td><td style="text-align:left;">Carter</td></tr></tbody></table><p>现在，我们希望查找 &quot;OrderPrice&quot; 列的第一个值。</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">FIRST</span><span class="token punctuation">(</span>OrderPrice<span class="token punctuation">)</span> <span class="token keyword">AS</span> FirstOrderPrice <span class="token keyword">FROM</span> Orders
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>结果集：</p><table><thead><tr><th style="text-align:left;">FirstOrderPrice</th></tr></thead><tbody><tr><td style="text-align:left;">1000</td></tr></tbody></table><h2 id="sql-last-函数" tabindex="-1"><a class="header-anchor" href="#sql-last-函数" aria-hidden="true">#</a> SQL LAST() 函数</h2><blockquote><p>LAST() 函数返回指定的字段中最后一个记录的值</p><p>tips:可使用 ORDER BY 语句来对记录进行排序</p></blockquote><h3 id="sql-last-语法" tabindex="-1"><a class="header-anchor" href="#sql-last-语法" aria-hidden="true">#</a> SQL LAST() 语法</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">LAST</span><span class="token punctuation">(</span>column_name<span class="token punctuation">)</span> <span class="token keyword">FROM</span> table_name
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h3 id="sql-last-实例" tabindex="-1"><a class="header-anchor" href="#sql-last-实例" aria-hidden="true">#</a> SQL LAST() 实例</h3><p>我们拥有下面这个 &quot;Orders&quot; 表：</p><table><thead><tr><th style="text-align:left;">O_Id</th><th style="text-align:left;">OrderDate</th><th style="text-align:left;">OrderPrice</th><th style="text-align:left;">Customer</th></tr></thead><tbody><tr><td style="text-align:left;">1</td><td style="text-align:left;">2008/12/29</td><td style="text-align:left;">1000</td><td style="text-align:left;">Bush</td></tr><tr><td style="text-align:left;">2</td><td style="text-align:left;">2008/11/23</td><td style="text-align:left;">1600</td><td style="text-align:left;">Carter</td></tr><tr><td style="text-align:left;">3</td><td style="text-align:left;">2008/10/05</td><td style="text-align:left;">700</td><td style="text-align:left;">Bush</td></tr><tr><td style="text-align:left;">4</td><td style="text-align:left;">2008/09/28</td><td style="text-align:left;">300</td><td style="text-align:left;">Bush</td></tr><tr><td style="text-align:left;">5</td><td style="text-align:left;">2008/08/06</td><td style="text-align:left;">2000</td><td style="text-align:left;">Adams</td></tr><tr><td style="text-align:left;">6</td><td style="text-align:left;">2008/07/21</td><td style="text-align:left;">100</td><td style="text-align:left;">Carter</td></tr></tbody></table><p>现在，我们希望查找 &quot;OrderPrice&quot; 列的最后一个值。</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">LAST</span><span class="token punctuation">(</span>OrderPrice<span class="token punctuation">)</span> <span class="token keyword">AS</span> LastOrderPrice <span class="token keyword">FROM</span> Orders
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>结果集:</p><table><thead><tr><th style="text-align:left;">LastOrderPrice</th></tr></thead><tbody><tr><td style="text-align:left;">100</td></tr></tbody></table><h2 id="sql-max-函数" tabindex="-1"><a class="header-anchor" href="#sql-max-函数" aria-hidden="true">#</a> SQL MAX() 函数</h2><blockquote><p>MAX() 函数返回一列中的最大值。Null 值不包括在计算中</p></blockquote><h3 id="sql-max-语法" tabindex="-1"><a class="header-anchor" href="#sql-max-语法" aria-hidden="true">#</a> SQL MAX() 语法</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">MAX</span><span class="token punctuation">(</span>column_name<span class="token punctuation">)</span> <span class="token keyword">FROM</span> table_name
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>注释：MIN 和 MAX 也可用于文本列，以获得按字母顺序排列的最高或最低值</p><h3 id="sql-max-实例" tabindex="-1"><a class="header-anchor" href="#sql-max-实例" aria-hidden="true">#</a> SQL MAX() 实例</h3><p>我们拥有下面这个 &quot;Orders&quot; 表：</p><table><thead><tr><th style="text-align:left;">O_Id</th><th style="text-align:left;">OrderDate</th><th style="text-align:left;">OrderPrice</th><th style="text-align:left;">Customer</th></tr></thead><tbody><tr><td style="text-align:left;">1</td><td style="text-align:left;">2008/12/29</td><td style="text-align:left;">1000</td><td style="text-align:left;">Bush</td></tr><tr><td style="text-align:left;">2</td><td style="text-align:left;">2008/11/23</td><td style="text-align:left;">1600</td><td style="text-align:left;">Carter</td></tr><tr><td style="text-align:left;">3</td><td style="text-align:left;">2008/10/05</td><td style="text-align:left;">700</td><td style="text-align:left;">Bush</td></tr><tr><td style="text-align:left;">4</td><td style="text-align:left;">2008/09/28</td><td style="text-align:left;">300</td><td style="text-align:left;">Bush</td></tr><tr><td style="text-align:left;">5</td><td style="text-align:left;">2008/08/06</td><td style="text-align:left;">2000</td><td style="text-align:left;">Adams</td></tr><tr><td style="text-align:left;">6</td><td style="text-align:left;">2008/07/21</td><td style="text-align:left;">100</td><td style="text-align:left;">Carter</td></tr></tbody></table><p>现在，我们希望查找 &quot;OrderPrice&quot; 列的最大值。</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">MAX</span><span class="token punctuation">(</span>OrderPrice<span class="token punctuation">)</span> <span class="token keyword">AS</span> LargestOrderPrice <span class="token keyword">FROM</span> Orders
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>结果集：</p><table><thead><tr><th style="text-align:left;">LargestOrderPrice</th></tr></thead><tbody><tr><td style="text-align:left;">2000</td></tr></tbody></table><h2 id="sql-min-函数" tabindex="-1"><a class="header-anchor" href="#sql-min-函数" aria-hidden="true">#</a> SQL MIN() 函数</h2><blockquote><p>MIN() 函数返回一列中最小的值。Null 值不包括在计算中</p></blockquote><h3 id="sql-min-语法" tabindex="-1"><a class="header-anchor" href="#sql-min-语法" aria-hidden="true">#</a> SQL MIN() 语法</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">MIN</span><span class="token punctuation">(</span>column_name<span class="token punctuation">)</span> <span class="token keyword">FROM</span> table_name 
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>注释：MIN和MAX 也可用于文本列，以获得按字母顺序排列的最高或最低值</p><h3 id="sql-min-实例" tabindex="-1"><a class="header-anchor" href="#sql-min-实例" aria-hidden="true">#</a> SQL MIN() 实例</h3><p>我们拥有下面这个 &quot;Orders&quot; 表：</p><table><thead><tr><th style="text-align:left;">O_Id</th><th style="text-align:left;">OrderDate</th><th style="text-align:left;">OrderPrice</th><th style="text-align:left;">Customer</th></tr></thead><tbody><tr><td style="text-align:left;">1</td><td style="text-align:left;">2008/12/29</td><td style="text-align:left;">1000</td><td style="text-align:left;">Bush</td></tr><tr><td style="text-align:left;">2</td><td style="text-align:left;">2008/11/23</td><td style="text-align:left;">1600</td><td style="text-align:left;">Carter</td></tr><tr><td style="text-align:left;">3</td><td style="text-align:left;">2008/10/05</td><td style="text-align:left;">700</td><td style="text-align:left;">Bush</td></tr><tr><td style="text-align:left;">4</td><td style="text-align:left;">2008/09/28</td><td style="text-align:left;">300</td><td style="text-align:left;">Bush</td></tr><tr><td style="text-align:left;">5</td><td style="text-align:left;">2008/08/06</td><td style="text-align:left;">2000</td><td style="text-align:left;">Adams</td></tr><tr><td style="text-align:left;">6</td><td style="text-align:left;">2008/07/21</td><td style="text-align:left;">100</td><td style="text-align:left;">Carter</td></tr></tbody></table><p>现在，我们希望查找 &quot;OrderPrice&quot; 列的最小值。</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">MIN</span><span class="token punctuation">(</span>OrderPrice<span class="token punctuation">)</span> <span class="token keyword">AS</span> SmallestOrderPrice <span class="token keyword">FROM</span> Orders
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>结果集：</p><table><thead><tr><th style="text-align:left;">SmallestOrderPrice</th></tr></thead><tbody><tr><td style="text-align:left;">100</td></tr></tbody></table><h2 id="sql-sum-函数" tabindex="-1"><a class="header-anchor" href="#sql-sum-函数" aria-hidden="true">#</a> SQL SUM() 函数</h2><blockquote><p>SUM 函数返回数值列的总数</p></blockquote><h3 id="sql-sum-语法" tabindex="-1"><a class="header-anchor" href="#sql-sum-语法" aria-hidden="true">#</a> SQL SUM() 语法</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">SUM</span><span class="token punctuation">(</span>column_name<span class="token punctuation">)</span> <span class="token keyword">FROM</span> table_name
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h3 id="sql-sum-实例" tabindex="-1"><a class="header-anchor" href="#sql-sum-实例" aria-hidden="true">#</a> SQL SUM() 实例</h3><p>我们拥有下面这个 &quot;Orders&quot; 表：</p><table><thead><tr><th style="text-align:left;">O_Id</th><th style="text-align:left;">OrderDate</th><th style="text-align:left;">OrderPrice</th><th style="text-align:left;">Customer</th></tr></thead><tbody><tr><td style="text-align:left;">1</td><td style="text-align:left;">2008/12/29</td><td style="text-align:left;">1000</td><td style="text-align:left;">Bush</td></tr><tr><td style="text-align:left;">2</td><td style="text-align:left;">2008/11/23</td><td style="text-align:left;">1600</td><td style="text-align:left;">Carter</td></tr><tr><td style="text-align:left;">3</td><td style="text-align:left;">2008/10/05</td><td style="text-align:left;">700</td><td style="text-align:left;">Bush</td></tr><tr><td style="text-align:left;">4</td><td style="text-align:left;">2008/09/28</td><td style="text-align:left;">300</td><td style="text-align:left;">Bush</td></tr><tr><td style="text-align:left;">5</td><td style="text-align:left;">2008/08/06</td><td style="text-align:left;">2000</td><td style="text-align:left;">Adams</td></tr><tr><td style="text-align:left;">6</td><td style="text-align:left;">2008/07/21</td><td style="text-align:left;">100</td><td style="text-align:left;">Carter</td></tr></tbody></table><p>现在，我们希望查找 &quot;OrderPrice&quot; 字段的总数。</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">SUM</span><span class="token punctuation">(</span>OrderPrice<span class="token punctuation">)</span> <span class="token keyword">AS</span> OrderTotal <span class="token keyword">FROM</span> Orders
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>结果集：</p><table><thead><tr><th style="text-align:left;">OrderTotal</th></tr></thead><tbody><tr><td style="text-align:left;">5700</td></tr></tbody></table><h2 id="sql-group-by-语句" tabindex="-1"><a class="header-anchor" href="#sql-group-by-语句" aria-hidden="true">#</a> SQL GROUP BY 语句</h2><blockquote><p>GROUP BY语句用于结合合计函数，根据一个或多个列对结果集进行分组</p></blockquote><h3 id="sql-group-by-语法" tabindex="-1"><a class="header-anchor" href="#sql-group-by-语法" aria-hidden="true">#</a> SQL GROUP BY 语法</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> column_name<span class="token punctuation">,</span>aggregate_function<span class="token punctuation">(</span>column_name<span class="token punctuation">)</span>
<span class="token keyword">FROM</span> table_name
<span class="token keyword">WHERE</span> column_name operator <span class="token keyword">value</span>
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> column_name
</code></pre><div class="line-numbers"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><h3 id="sql-group-by-实例" tabindex="-1"><a class="header-anchor" href="#sql-group-by-实例" aria-hidden="true">#</a> SQL GROUP BY 实例</h3><p>我们拥有下面这个 &quot;Orders&quot; 表：</p><table><thead><tr><th style="text-align:left;">O_Id</th><th style="text-align:left;">OrderDate</th><th style="text-align:left;">OrderPrice</th><th style="text-align:left;">Customer</th></tr></thead><tbody><tr><td style="text-align:left;">1</td><td style="text-align:left;">2008/12/29</td><td style="text-align:left;">1000</td><td style="text-align:left;">Bush</td></tr><tr><td style="text-align:left;">2</td><td style="text-align:left;">2008/11/23</td><td style="text-align:left;">1600</td><td style="text-align:left;">Carter</td></tr><tr><td style="text-align:left;">3</td><td style="text-align:left;">2008/10/05</td><td style="text-align:left;">700</td><td style="text-align:left;">Bush</td></tr><tr><td style="text-align:left;">4</td><td style="text-align:left;">2008/09/28</td><td style="text-align:left;">300</td><td style="text-align:left;">Bush</td></tr><tr><td style="text-align:left;">5</td><td style="text-align:left;">2008/08/06</td><td style="text-align:left;">2000</td><td style="text-align:left;">Adams</td></tr><tr><td style="text-align:left;">6</td><td style="text-align:left;">2008/07/21</td><td style="text-align:left;">100</td><td style="text-align:left;">Carter</td></tr></tbody></table><p>现在，我们希望查找每个客户的总金额（总订单）。</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> Customer<span class="token punctuation">,</span><span class="token function">SUM</span><span class="token punctuation">(</span>OrderPrice<span class="token punctuation">)</span> <span class="token keyword">FROM</span> Orders 
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> Customer
</code></pre><div class="line-numbers"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p>结果集：</p><table><thead><tr><th style="text-align:left;">Customer</th><th style="text-align:left;">SUM(OrderPrice)</th></tr></thead><tbody><tr><td style="text-align:left;">Bush</td><td style="text-align:left;">2000</td></tr><tr><td style="text-align:left;">Carter</td><td style="text-align:left;">1700</td></tr><tr><td style="text-align:left;">Adams</td><td style="text-align:left;">2000</td></tr></tbody></table><h2 id="sql-having-子句" tabindex="-1"><a class="header-anchor" href="#sql-having-子句" aria-hidden="true">#</a> SQL HAVING 子句</h2><blockquote><p>在SQL 中增加HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用</p></blockquote><h3 id="sql-having-语法" tabindex="-1"><a class="header-anchor" href="#sql-having-语法" aria-hidden="true">#</a> SQL HAVING 语法</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> column_name<span class="token punctuation">,</span>aggregate_function<span class="token punctuation">(</span>column_name<span class="token punctuation">)</span>
<span class="token keyword">FROM</span> table_name
<span class="token keyword">WHERE</span> column_name operator <span class="token keyword">value</span>
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> column_name
<span class="token keyword">HAVING</span> aggregate_function<span class="token punctuation">(</span>column_name<span class="token punctuation">)</span> operator <span class="token keyword">value</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><h3 id="sql-having-实例" tabindex="-1"><a class="header-anchor" href="#sql-having-实例" aria-hidden="true">#</a> SQL HAVING 实例</h3><p>我们拥有下面这个 &quot;Orders&quot; 表：</p><table><thead><tr><th style="text-align:left;">O_Id</th><th style="text-align:left;">OrderDate</th><th style="text-align:left;">OrderPrice</th><th style="text-align:left;">Customer</th></tr></thead><tbody><tr><td style="text-align:left;">1</td><td style="text-align:left;">2008/12/29</td><td style="text-align:left;">1000</td><td style="text-align:left;">Bush</td></tr><tr><td style="text-align:left;">2</td><td style="text-align:left;">2008/11/23</td><td style="text-align:left;">1600</td><td style="text-align:left;">Carter</td></tr><tr><td style="text-align:left;">3</td><td style="text-align:left;">2008/10/05</td><td style="text-align:left;">700</td><td style="text-align:left;">Bush</td></tr><tr><td style="text-align:left;">4</td><td style="text-align:left;">2008/09/28</td><td style="text-align:left;">300</td><td style="text-align:left;">Bush</td></tr><tr><td style="text-align:left;">5</td><td style="text-align:left;">2008/08/06</td><td style="text-align:left;">2000</td><td style="text-align:left;">Adams</td></tr><tr><td style="text-align:left;">6</td><td style="text-align:left;">2008/07/21</td><td style="text-align:left;">100</td><td style="text-align:left;">Carter</td></tr></tbody></table><p>现在，我们希望查找订单总金额少于 2000 的客户。</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> Customer<span class="token punctuation">,</span><span class="token function">SUM</span><span class="token punctuation">(</span>OrderPrice<span class="token punctuation">)</span> <span class="token keyword">FROM</span> Orders
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> Customer
<span class="token keyword">HAVING</span> <span class="token function">SUM</span><span class="token punctuation">(</span>OrderPrice<span class="token punctuation">)</span> <span class="token operator">&lt;</span><span class="token number">2000</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p>结果集：</p><table><thead><tr><th style="text-align:left;">Customer</th><th style="text-align:left;">SUM(OrderPrice)</th></tr></thead><tbody><tr><td style="text-align:left;">Carter</td><td style="text-align:left;">1700</td></tr></tbody></table><p>现在我们希望查找客户 &quot;Bush&quot; 或 &quot;Adams&quot; 拥有超过 1500 的订单总金额。</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> Customer<span class="token punctuation">,</span><span class="token function">SUM</span><span class="token punctuation">(</span>Orderprice<span class="token punctuation">)</span> <span class="token keyword">FROM</span> Orders
<span class="token keyword">WHERE</span> Customer<span class="token operator">=</span><span class="token string">&#39;Bush&#39;</span> <span class="token operator">OR</span> Customer<span class="token operator">=</span><span class="token string">&#39;Adams&#39;</span>
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> Customer
<span class="token keyword">HAVING</span> <span class="token function">SUM</span><span class="token punctuation">(</span>OrderPrice<span class="token punctuation">)</span> <span class="token operator">&gt;</span> <span class="token number">1500</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>结果集：</p><p>结果集：</p><table><thead><tr><th style="text-align:left;">Customer</th><th style="text-align:left;">SUM(OrderPrice)</th></tr></thead><tbody><tr><td style="text-align:left;">Bush</td><td style="text-align:left;">2000</td></tr><tr><td style="text-align:left;">Adams</td><td style="text-align:left;">2000</td></tr></tbody></table><h2 id="sql-ucase-函数" tabindex="-1"><a class="header-anchor" href="#sql-ucase-函数" aria-hidden="true">#</a> SQL UCASE() 函数</h2><blockquote><p>UCASE 函数吧字段的值转为大写</p></blockquote><h3 id="sql-ucase-语法" tabindex="-1"><a class="header-anchor" href="#sql-ucase-语法" aria-hidden="true">#</a> SQL UCASE() 语法</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">UCASE</span><span class="token punctuation">(</span>column_name<span class="token punctuation">)</span> <span class="token keyword">FROM</span> table_name
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h3 id="sql-ucase-实例" tabindex="-1"><a class="header-anchor" href="#sql-ucase-实例" aria-hidden="true">#</a> SQL UCASE() 实例</h3><p>我们拥有下面这个 &quot;Persons&quot; 表：</p><table><thead><tr><th style="text-align:left;">Id</th><th style="text-align:left;">LastName</th><th style="text-align:left;">FirstName</th><th style="text-align:left;">Address</th><th style="text-align:left;">City</th></tr></thead><tbody><tr><td style="text-align:left;">1</td><td style="text-align:left;">Adams</td><td style="text-align:left;">John</td><td style="text-align:left;">Oxford Street</td><td style="text-align:left;">London</td></tr><tr><td style="text-align:left;">2</td><td style="text-align:left;">Bush</td><td style="text-align:left;">George</td><td style="text-align:left;">Fifth Avenue</td><td style="text-align:left;">New York</td></tr><tr><td style="text-align:left;">3</td><td style="text-align:left;">Carter</td><td style="text-align:left;">Thomas</td><td style="text-align:left;">Changan Street</td><td style="text-align:left;">Beijing</td></tr></tbody></table><p>现在，我们希望选取 &quot;LastName&quot; 和 &quot;FirstName&quot; 列的内容，然后把 &quot;LastName&quot; 列转换为大写。</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">UCASE</span><span class="token punctuation">(</span>LastName<span class="token punctuation">)</span> <span class="token keyword">as</span> LastName<span class="token punctuation">,</span>FirstName <span class="token keyword">FROM</span> Persons
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>结果集：</p><table><thead><tr><th style="text-align:left;">LastName</th><th style="text-align:left;">FirstName</th></tr></thead><tbody><tr><td style="text-align:left;">ADAMS</td><td style="text-align:left;">John</td></tr><tr><td style="text-align:left;">BUSH</td><td style="text-align:left;">George</td></tr><tr><td style="text-align:left;">CARTER</td><td style="text-align:left;">Thomas</td></tr></tbody></table><h2 id="sql-lcase-函数" tabindex="-1"><a class="header-anchor" href="#sql-lcase-函数" aria-hidden="true">#</a> SQL LCASE() 函数</h2><blockquote><p>LCASE 函数把字段的值转为小写</p></blockquote><h3 id="sql-lcase-语法" tabindex="-1"><a class="header-anchor" href="#sql-lcase-语法" aria-hidden="true">#</a> SQL LCASE() 语法</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">LCASE</span><span class="token punctuation">(</span>column_name<span class="token punctuation">)</span> <span class="token keyword">FROM</span> table_name
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h3 id="sql-lcase-实例" tabindex="-1"><a class="header-anchor" href="#sql-lcase-实例" aria-hidden="true">#</a> SQL LCASE() 实例</h3><p>我们拥有下面这个 &quot;Persons&quot; 表：</p><table><thead><tr><th style="text-align:left;">Id</th><th style="text-align:left;">LastName</th><th style="text-align:left;">FirstName</th><th style="text-align:left;">Address</th><th style="text-align:left;">City</th></tr></thead><tbody><tr><td style="text-align:left;">1</td><td style="text-align:left;">Adams</td><td style="text-align:left;">John</td><td style="text-align:left;">Oxford Street</td><td style="text-align:left;">London</td></tr><tr><td style="text-align:left;">2</td><td style="text-align:left;">Bush</td><td style="text-align:left;">George</td><td style="text-align:left;">Fifth Avenue</td><td style="text-align:left;">New York</td></tr><tr><td style="text-align:left;">3</td><td style="text-align:left;">Carter</td><td style="text-align:left;">Thomas</td><td style="text-align:left;">Changan Street</td><td style="text-align:left;">Beijing</td></tr></tbody></table><p>现在，我们希望选取 &quot;LastName&quot; 和 &quot;FirstName&quot; 列的内容，然后把 &quot;LastName&quot; 列转换为小写。</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">LCASE</span><span class="token punctuation">(</span>LastName<span class="token punctuation">)</span> <span class="token keyword">as</span> LastName<span class="token punctuation">,</span>FirstName <span class="token keyword">FROM</span> Persons
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>结果集：</p><table><thead><tr><th style="text-align:left;">LastName</th><th style="text-align:left;">FirstName</th></tr></thead><tbody><tr><td style="text-align:left;">adams</td><td style="text-align:left;">John</td></tr><tr><td style="text-align:left;">bush</td><td style="text-align:left;">George</td></tr><tr><td style="text-align:left;">carter</td><td style="text-align:left;">Thomas</td></tr></tbody></table><h2 id="sql-mid-函数" tabindex="-1"><a class="header-anchor" href="#sql-mid-函数" aria-hidden="true">#</a> SQL MID() 函数</h2><blockquote><p>MID 函数用于从文本段中提取字符</p></blockquote><h3 id="sql-mid-语法" tabindex="-1"><a class="header-anchor" href="#sql-mid-语法" aria-hidden="true">#</a> SQL MID() 语法</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">MID</span><span class="token punctuation">(</span>column_name<span class="token punctuation">,</span><span class="token keyword">start</span><span class="token punctuation">[</span><span class="token punctuation">,</span>length<span class="token punctuation">]</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> table_name
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><table><thead><tr><th style="text-align:left;">参数</th><th style="text-align:left;">描述</th></tr></thead><tbody><tr><td style="text-align:left;">column_name</td><td style="text-align:left;">必需。要提取字符的字段。</td></tr><tr><td style="text-align:left;">start</td><td style="text-align:left;">必需。规定开始位置（起始值是 1）。</td></tr><tr><td style="text-align:left;">length</td><td style="text-align:left;">可选。要返回的字符数。如果省略，则 MID() 函数返回剩余文本。</td></tr></tbody></table><h3 id="sql-mid-实例" tabindex="-1"><a class="header-anchor" href="#sql-mid-实例" aria-hidden="true">#</a> SQL MID() 实例</h3><p>我们拥有下面这个 &quot;Persons&quot; 表：</p><table><thead><tr><th style="text-align:left;">Id</th><th style="text-align:left;">LastName</th><th style="text-align:left;">FirstName</th><th style="text-align:left;">Address</th><th style="text-align:left;">City</th></tr></thead><tbody><tr><td style="text-align:left;">1</td><td style="text-align:left;">Adams</td><td style="text-align:left;">John</td><td style="text-align:left;">Oxford Street</td><td style="text-align:left;">London</td></tr><tr><td style="text-align:left;">2</td><td style="text-align:left;">Bush</td><td style="text-align:left;">George</td><td style="text-align:left;">Fifth Avenue</td><td style="text-align:left;">New York</td></tr><tr><td style="text-align:left;">3</td><td style="text-align:left;">Carter</td><td style="text-align:left;">Thomas</td><td style="text-align:left;">Changan Street</td><td style="text-align:left;">Beijing</td></tr></tbody></table><p>现在，我们希望从 &quot;City&quot; 列中提取前 3 个字符。</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">MID</span><span class="token punctuation">(</span>City<span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span> <span class="token keyword">as</span> SmailCity <span class="token keyword">FROM</span> Persons
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>结果集：</p><table><thead><tr><th style="text-align:left;">SmallCity</th></tr></thead><tbody><tr><td style="text-align:left;">Lon</td></tr><tr><td style="text-align:left;">New</td></tr><tr><td style="text-align:left;">Bei</td></tr></tbody></table><h2 id="sql-len-函数" tabindex="-1"><a class="header-anchor" href="#sql-len-函数" aria-hidden="true">#</a> SQL LEN() 函数</h2><blockquote><p>LEN 函数返回文本字段中值的长度</p></blockquote><h3 id="sql-len-语法" tabindex="-1"><a class="header-anchor" href="#sql-len-语法" aria-hidden="true">#</a> SQL LEN() 语法</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">LEN</span><span class="token punctuation">(</span>column_name<span class="token punctuation">)</span> <span class="token keyword">FROM</span> table_name
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h3 id="sql-len-实例" tabindex="-1"><a class="header-anchor" href="#sql-len-实例" aria-hidden="true">#</a> SQL LEN() 实例</h3><p>我们拥有下面这个 &quot;Persons&quot; 表：</p><table><thead><tr><th style="text-align:left;">Id</th><th style="text-align:left;">LastName</th><th style="text-align:left;">FirstName</th><th style="text-align:left;">Address</th><th style="text-align:left;">City</th></tr></thead><tbody><tr><td style="text-align:left;">1</td><td style="text-align:left;">Adams</td><td style="text-align:left;">John</td><td style="text-align:left;">Oxford Street</td><td style="text-align:left;">London</td></tr><tr><td style="text-align:left;">2</td><td style="text-align:left;">Bush</td><td style="text-align:left;">George</td><td style="text-align:left;">Fifth Avenue</td><td style="text-align:left;">New York</td></tr><tr><td style="text-align:left;">3</td><td style="text-align:left;">Carter</td><td style="text-align:left;">Thomas</td><td style="text-align:left;">Changan Street</td><td style="text-align:left;">Beijing</td></tr></tbody></table><p>现在，我们希望取得 &quot;City&quot; 列中值的长度。</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">LEN</span><span class="token punctuation">(</span>City<span class="token punctuation">)</span> <span class="token keyword">as</span> LengthOfCity <span class="token keyword">FROM</span> Persons
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>结果集：</p><table><thead><tr><th style="text-align:left;">LengthOfCity</th></tr></thead><tbody><tr><td style="text-align:left;">6</td></tr><tr><td style="text-align:left;">8</td></tr><tr><td style="text-align:left;">7</td></tr></tbody></table><h2 id="sql-round-函数" tabindex="-1"><a class="header-anchor" href="#sql-round-函数" aria-hidden="true">#</a> SQL ROUND() 函数</h2><blockquote><p>ROUND 函数用于把数值字段舍入为指定的小数位数</p></blockquote><h3 id="sql-round-语法" tabindex="-1"><a class="header-anchor" href="#sql-round-语法" aria-hidden="true">#</a> SQL ROUND() 语法</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">ROUND</span><span class="token punctuation">(</span>column_name<span class="token punctuation">,</span>decimals<span class="token punctuation">)</span> <span class="token keyword">FROM</span> table_name
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><table><thead><tr><th style="text-align:left;">参数</th><th style="text-align:left;">描述</th></tr></thead><tbody><tr><td style="text-align:left;">column_name</td><td style="text-align:left;">必需。要舍入的字段。</td></tr><tr><td style="text-align:left;">decimals</td><td style="text-align:left;">必需。规定要返回的小数位数。</td></tr></tbody></table><h3 id="sql-round-实例" tabindex="-1"><a class="header-anchor" href="#sql-round-实例" aria-hidden="true">#</a> SQL ROUND() 实例</h3><p>我们拥有下面这个 &quot;Products&quot; 表：</p><table><thead><tr><th style="text-align:left;">Prod_Id</th><th style="text-align:left;">ProductName</th><th style="text-align:left;">Unit</th><th style="text-align:left;">UnitPrice</th></tr></thead><tbody><tr><td style="text-align:left;">1</td><td style="text-align:left;">gold</td><td style="text-align:left;">1000 g</td><td style="text-align:left;">32.35</td></tr><tr><td style="text-align:left;">2</td><td style="text-align:left;">silver</td><td style="text-align:left;">1000 g</td><td style="text-align:left;">11.56</td></tr><tr><td style="text-align:left;">3</td><td style="text-align:left;">copper</td><td style="text-align:left;">1000 g</td><td style="text-align:left;">6.85</td></tr></tbody></table><p>现在，我们希望把名称和价格舍入为最接近的整数。</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> ProductName<span class="token punctuation">,</span><span class="token function">ROUND</span><span class="token punctuation">(</span>UnitPrice<span class="token punctuation">,</span><span class="token number">0</span><span class="token punctuation">)</span> <span class="token keyword">as</span> UnitPrice <span class="token keyword">FROM</span> Products
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>结果集：</p><table><thead><tr><th style="text-align:left;">ProductName</th><th style="text-align:left;">UnitPrice</th></tr></thead><tbody><tr><td style="text-align:left;">gold</td><td style="text-align:left;">32</td></tr><tr><td style="text-align:left;">silver</td><td style="text-align:left;">12</td></tr><tr><td style="text-align:left;">copper</td><td style="text-align:left;">7</td></tr></tbody></table><h2 id="sql-now-函数" tabindex="-1"><a class="header-anchor" href="#sql-now-函数" aria-hidden="true">#</a> SQL NOW() 函数</h2><blockquote><p>NOW 函数返回当前的日期和时间</p></blockquote><h3 id="sql-now-语法" tabindex="-1"><a class="header-anchor" href="#sql-now-语法" aria-hidden="true">#</a> SQL NOW() 语法</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> table_name
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><h3 id="sql-now-实例" tabindex="-1"><a class="header-anchor" href="#sql-now-实例" aria-hidden="true">#</a> SQL NOW() 实例</h3><p>我们拥有下面这个 &quot;Products&quot; 表：</p><table><thead><tr><th style="text-align:left;">Prod_Id</th><th style="text-align:left;">ProductName</th><th style="text-align:left;">Unit</th><th style="text-align:left;">UnitPrice</th></tr></thead><tbody><tr><td style="text-align:left;">1</td><td style="text-align:left;">gold</td><td style="text-align:left;">1000 g</td><td style="text-align:left;">32.35</td></tr><tr><td style="text-align:left;">2</td><td style="text-align:left;">silver</td><td style="text-align:left;">1000 g</td><td style="text-align:left;">11.56</td></tr><tr><td style="text-align:left;">3</td><td style="text-align:left;">copper</td><td style="text-align:left;">1000 g</td><td style="text-align:left;">6.85</td></tr></tbody></table><p>现在，我们希望显示当天的日期所对应的名称和价格。</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> ProductName<span class="token punctuation">,</span>UnitPrice<span class="token punctuation">,</span><span class="token function">Now</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">as</span> PerDate <span class="token keyword">FROM</span> Products
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>结果集：</p><table><thead><tr><th style="text-align:left;">ProductName</th><th style="text-align:left;">UnitPrice</th><th style="text-align:left;">PerDate</th></tr></thead><tbody><tr><td style="text-align:left;">gold</td><td style="text-align:left;">32.35</td><td style="text-align:left;">12/29/2008 11:36:05 AM</td></tr><tr><td style="text-align:left;">silver</td><td style="text-align:left;">11.56</td><td style="text-align:left;">12/29/2008 11:36:05 AM</td></tr><tr><td style="text-align:left;">copper</td><td style="text-align:left;">6.85</td><td style="text-align:left;">12/29/2008 11:36:05 AM</td></tr></tbody></table><h2 id="sql-总结" tabindex="-1"><a class="header-anchor" href="#sql-总结" aria-hidden="true">#</a> SQL 总结</h2><h3 id="sql-语句" tabindex="-1"><a class="header-anchor" href="#sql-语句" aria-hidden="true">#</a> SQL 语句</h3><table><thead><tr><th style="text-align:left;">语句</th><th style="text-align:left;">语法</th></tr></thead><tbody><tr><td style="text-align:left;">AND / OR</td><td style="text-align:left;">SELECT column_name(s) FROM table_name WHERE condition AND|OR condition</td></tr><tr><td style="text-align:left;">ALTER TABLE (add column)</td><td style="text-align:left;">ALTER TABLE table_name ADD column_name datatype</td></tr><tr><td style="text-align:left;">ALTER TABLE (drop column)</td><td style="text-align:left;">ALTER TABLE table_name DROP COLUMN column_name</td></tr><tr><td style="text-align:left;">AS (alias for column)</td><td style="text-align:left;">SELECT column_name AS column_alias FROM table_name</td></tr><tr><td style="text-align:left;">AS (alias for table)</td><td style="text-align:left;">SELECT column_name FROM table_name AS table_alias</td></tr><tr><td style="text-align:left;">BETWEEN</td><td style="text-align:left;">SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2</td></tr><tr><td style="text-align:left;">CREATE DATABASE</td><td style="text-align:left;">CREATE DATABASE database_name</td></tr><tr><td style="text-align:left;">CREATE INDEX</td><td style="text-align:left;">CREATE INDEX index_name ON table_name (column_name)</td></tr><tr><td style="text-align:left;">CREATE TABLE</td><td style="text-align:left;">CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, ....... )</td></tr><tr><td style="text-align:left;">CREATE UNIQUE INDEX</td><td style="text-align:left;">CREATE UNIQUE INDEX index_name ON table_name (column_name)</td></tr><tr><td style="text-align:left;">CREATE VIEW</td><td style="text-align:left;">CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition</td></tr><tr><td style="text-align:left;">DELETE FROM</td><td style="text-align:left;">DELETE FROM table_name (<strong>Note:</strong> Deletes the entire table!!)<em>or</em>DELETE FROM table_name WHERE condition</td></tr><tr><td style="text-align:left;">DROP DATABASE</td><td style="text-align:left;">DROP DATABASE database_name</td></tr><tr><td style="text-align:left;">DROP INDEX</td><td style="text-align:left;">DROP INDEX table_name.index_name</td></tr><tr><td style="text-align:left;">DROP TABLE</td><td style="text-align:left;">DROP TABLE table_name</td></tr><tr><td style="text-align:left;">GROUP BY</td><td style="text-align:left;">SELECT column_name1,SUM(column_name2) FROM table_name GROUP BY column_name1</td></tr><tr><td style="text-align:left;">HAVING</td><td style="text-align:left;">SELECT column_name1,SUM(column_name2) FROM table_name GROUP BY column_name1 HAVING SUM(column_name2) condition value</td></tr><tr><td style="text-align:left;">IN</td><td style="text-align:left;">SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,..)</td></tr><tr><td style="text-align:left;">INSERT INTO</td><td style="text-align:left;">INSERT INTO table_name VALUES (value1, value2,....)<em>or</em>INSERT INTO table_name (column_name1, column_name2,...) VALUES (value1, value2,....)</td></tr><tr><td style="text-align:left;">LIKE</td><td style="text-align:left;">SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern</td></tr><tr><td style="text-align:left;">ORDER BY</td><td style="text-align:left;">SELECT column_name(s) FROM table_name ORDER BY column_name [ASC|DESC]</td></tr><tr><td style="text-align:left;">SELECT</td><td style="text-align:left;">SELECT column_name(s) FROM table_name</td></tr><tr><td style="text-align:left;">SELECT *</td><td style="text-align:left;">SELECT * FROM table_name</td></tr><tr><td style="text-align:left;">SELECT DISTINCT</td><td style="text-align:left;">SELECT DISTINCT column_name(s) FROM table_name</td></tr><tr><td style="text-align:left;">SELECT INTO (used to create backup copies of tables)</td><td style="text-align:left;">SELECT * INTO new_table_name FROM original_table_name<em>or</em>SELECT column_name(s) INTO new_table_name FROM original_table_name</td></tr><tr><td style="text-align:left;">TRUNCATE TABLE (deletes only the data inside the table)</td><td style="text-align:left;">TRUNCATE TABLE table_name</td></tr><tr><td style="text-align:left;">UPDATE</td><td style="text-align:left;">UPDATE table_name SET column_name=new_value [, column_name=new_value] WHERE column_name=some_value</td></tr><tr><td style="text-align:left;">WHERE</td><td style="text-align:left;">SELECT column_name(s) FROM table_name WHERE condition</td></tr></tbody></table><h2 id="收集一些sql练习踩的坑" tabindex="-1"><a class="header-anchor" href="#收集一些sql练习踩的坑" aria-hidden="true">#</a> 收集一些SQL练习踩的坑</h2><p>👉： <a href="https://sqlzoo.net/wiki/" target="_blank" rel="noopener noreferrer">练习网站<span><svg class="icon outbound" xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewbox="0 0 100 100" width="15" height="15"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path><polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg><!--[--><span class="sr-only">open in new window</span><!--]--></span></a></p><table><thead><tr><th style="text-align:left;">name</th><th style="text-align:left;">continent</th></tr></thead><tbody><tr><td style="text-align:left;">Afghanistan</td><td style="text-align:left;">Asia</td></tr><tr><td style="text-align:left;">Albania</td><td style="text-align:left;">Europe</td></tr><tr><td style="text-align:left;">Algeria</td><td style="text-align:left;">Africa</td></tr><tr><td style="text-align:left;">Andorra</td><td style="text-align:left;">Europe</td></tr><tr><td style="text-align:left;">Angola</td><td style="text-align:left;">Africa</td></tr><tr><td style="text-align:left;">....</td><td style="text-align:left;"></td></tr></tbody></table><p>name:國家名稱 continent:洲份</p><p>world:表名</p><p>例子1:</p><p>找出所有首都和其國家名字,而首都要有國家名字中出現。</p><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> capital<span class="token punctuation">,</span>name <span class="token keyword">from</span> world <span class="token keyword">where</span> capital <span class="token operator">like</span> concat<span class="token punctuation">(</span><span class="token string">&#39;%&#39;</span><span class="token punctuation">,</span>name<span class="token punctuation">,</span><span class="token string">&#39;%&#39;</span><span class="token punctuation">)</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br></div></div><p>结果集：</p><table><thead><tr><th>capital</th><th>name</th></tr></thead><tbody><tr><td>Andorra la Vella</td><td>Andorra</td></tr><tr><td>Djibouti</td><td>Djibouti</td></tr><tr><td>Guatemala City</td><td>Guatemala</td></tr><tr><td>Kuwait City</td><td>Kuwait</td></tr><tr><td>Luxembourg</td><td>Luxembourg</td></tr><tr><td>Mexico City</td><td>Mexico</td></tr><tr><td>Monaco-Ville</td><td>Monaco</td></tr><tr><td>Panama City</td><td>Panama</td></tr><tr><td>San Marino</td><td>San Marino</td></tr></tbody></table><p>解析： SQL CONCAT函数用于将两个字符串连接起来，形成一个单一的字符串。</p><h2 id="补充" tabindex="-1"><a class="header-anchor" href="#补充" aria-hidden="true">#</a> 补充：</h2><h3 id="distinct-语法" tabindex="-1"><a class="header-anchor" href="#distinct-语法" aria-hidden="true">#</a> DISTINCT 语法：</h3><blockquote><p><code>DISTINCT</code> 关键字来指定某个或某些属性列唯一返回</p></blockquote><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token keyword">DISTINCT</span> <span class="token keyword">column</span><span class="token punctuation">,</span> another_column<span class="token punctuation">,</span> …
<span class="token keyword">FROM</span> mytable
<span class="token keyword">WHERE</span> condition<span class="token punctuation">(</span>s<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p>因为 <code>DISTINCT</code> 语法会直接删除重复的行, 我们还会学习 <code>GROUP BY</code> 语句， <code>GROUP BY</code> 也会返回唯一的行，不过可以对具有相同的 属性值的行做一些统计计算，比如：求和.</p><h3 id="limit-语法" tabindex="-1"><a class="header-anchor" href="#limit-语法" aria-hidden="true">#</a> LIMIT 语法：</h3><blockquote><p><code>LIMIT</code> 和 <code>OFFSET</code> 子句通常和<code>ORDER BY</code> 语句一起使用，当我们对整个结果集排序之后，我们可以 <code>LIMIT</code>来指定只返回多少行结果 ,用 <code>OFFSET</code>来指定从哪一行开始返回。你可以想象一下从一条长绳子剪下一小段的过程，我们通过 <code>OFFSET</code> 指定从哪里开始剪，用 <code>LIMIT</code> 指定剪下多少长度。</p></blockquote><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token keyword">column</span><span class="token punctuation">,</span> another_column<span class="token punctuation">,</span> …
<span class="token keyword">FROM</span> mytable
<span class="token keyword">WHERE</span> condition<span class="token punctuation">(</span>s<span class="token punctuation">)</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token keyword">column</span> <span class="token keyword">ASC</span><span class="token operator">/</span><span class="token keyword">DESC</span>
<span class="token keyword">LIMIT</span> num_limit <span class="token keyword">OFFSET</span> num_offset<span class="token punctuation">;</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p>LIMIT 结束值 OFFSET 启始值</p><p>例子：</p><p>如果按片长排列，John Lasseter导演导过片长第3长的电影是哪部？</p><table><thead><tr><th>Id</th><th>Title</th><th>Director</th><th>Year</th><th>Length_minutes</th></tr></thead><tbody><tr><td>1</td><td>Toy Story</td><td>John Lasseter</td><td>1995</td><td>81</td></tr><tr><td>3</td><td>Toy Story 2</td><td>John Lasseter</td><td>1999</td><td>93</td></tr><tr><td>2</td><td>A Bug&#39;s Life</td><td>John Lasseter</td><td>1998</td><td>95</td></tr><tr><td>7</td><td>Cars</td><td>John Lasseter</td><td>2006</td><td>117</td></tr><tr><td>12</td><td>Cars 2</td><td>John Lasseter</td><td>2011</td><td>120</td></tr></tbody></table><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> movies <span class="token keyword">where</span> Director <span class="token operator">=</span><span class="token string">&#39;John Lasseter&#39;</span> 
<span class="token keyword">order</span> <span class="token keyword">by</span> Length_minutes <span class="token keyword">LIMIT</span> <span class="token number">1</span> <span class="token keyword">OFFSET</span> <span class="token number">2</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p>结果集：</p><table><thead><tr><th>Id</th><th>Title</th><th>Director</th><th>Year</th><th>Length_minutes</th></tr></thead><tbody><tr><td>2</td><td>A Bug&#39;s Life</td><td>John Lasseter</td><td>1998</td><td>95</td></tr></tbody></table><h3 id="服务select查询语法" tabindex="-1"><a class="header-anchor" href="#服务select查询语法" aria-hidden="true">#</a> 服务select查询语法</h3><div class="language-sql ext-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token keyword">column</span><span class="token punctuation">,</span> another_column<span class="token punctuation">,</span> …
<span class="token keyword">FROM</span> mytable
<span class="token keyword">WHERE</span> condition<span class="token punctuation">(</span>s<span class="token punctuation">)</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token keyword">column</span> <span class="token keyword">ASC</span><span class="token operator">/</span><span class="token keyword">DESC</span>
<span class="token keyword">LIMIT</span> num_limit <span class="token keyword">OFFSET</span> num_offset<span class="token punctuation">;</span>
</code></pre><div class="line-numbers"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><!--]--></div><footer class="page-meta"><div class="meta-item edit-link"><!--[--><span class="aurora-nav-font aurora-navbar-icon"></span><a class="nav-link external nav-link-active meta-item-label" href="https://github.com/ranyong1997/sakura-docs/edit/main/docs/Back-end/SQL基础学习.md" rel="noopener noreferrer" target="_blank" aria-label="编辑"><!--[--><!--]--> 编辑<!--[--><!--]--></a><!--]--></div><div class="meta-item last-updated"><span class="meta-item-label">lastTime: </span><span class="meta-item-info">2022/2/21 上午12:17:46</span></div><div class="meta-item contributors"><span class="meta-item-label">Contributors: </span><span class="meta-item-info"><!--[--><!--[--><span class="contributor" title="email: 67427627+ranyong1997@users.noreply.github.com">luck_baby</span><!----><!--]--><!--]--></span></div></footer><!--[--><!--]--></main><main style="--borderRadius:10px;--opacity:1;" class="page box"><div class="theme-default-content"><!--[--><div style="--homePageImgApi:url(https://api.ixiaowai.cn/api/api.php?time=-236.27272727272728);" class="page-bottom-next"><div class="page-next-left page-next-item"><a aria-current="page" href="/Back-end/SQL%E5%9F%BA%E7%A1%80%E5%AD%A6%E4%B9%A0.html" class="router-link-active router-link-exact-active"><span></span></a></div><!----></div><!--]--><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--></div></main><div><!----></div><div class="recommend-page"><main style="--borderRadius:10px;--opacity:1;" class="page box"><h2 id="recommend-top-title">推荐阅读</h2><div id="recommend" class="recommend"><!--[--><!--]--></div></main></div><div id="control-comment" class="aurora-control-comment-box"><div style="--borderRadius: 10px;--opacity: 1;" class="theme-comment-box"><span class="aurora-comment-common aurora-iconfont-common page-comment-icon"></span><span class="aurora-comment-common aurora-comment-text">点击评论</span></div><div class="mobile-record aurora-comment-animate"><div class="page box" style="--borderRadius: 10px;--opacity: 1;"><div id="waline" class="vcomment-bottom theme-default-content"></div></div></div></div><!--]--><!--]--><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--></div><div id="page-sidebar-right" style="" class="page-sidebar-right"><div class="stickSidebar"><!--[--><div class="sidebar-cqy"></div><div style="--borderRadius: 10px;--opacity: 1;" id="stickSidebar" class="sidebar-single-enter-animate"><div class="sidebarScroll home-sidebar" style="" id="home-sidebar"><div id="" class="sidebar-single-common"><div class="home-sidebar-avatar"><img id="home-sidebar-avatar-img" src="https://gitee.com/ran_yong/mark-down-table-upload/raw/master/img/20211123222242.png" alt=""></div><div class="home-sidebar-info-desc"><span>Sakura</span></div><div class="home-sidebar-info-page"><div class="sidebar-page-common"><div>文章</div><div>19</div></div><div class="sidebar-page-common"><div>标签</div><div>24</div></div><div class="sidebar-page-common"><div>类别</div><div>4</div></div></div><a target="_blank" href="https://github.com/ranyong1997/sakura-docs"><div class="sidebar-github">Github</div></a><!--[--><!--]--></div><!--[--><!--]--><!----><!--[--><!--]--><div id="" class="sidebar-single-common"><div class="sidebar-link"><!--[--><!--]--></div></div><!----><!--[--><!--]--><!----><!--[--><!--]--><div id="" class="sidebar-single-enter-animate sidebar-single-page sidebar-single-common"><div class="change-page"><div class="catalog-page change-page-common"><span class="changePageActive" index="1">文章目录</span></div><div style="" class="latest-page change-page-common"><!----><span style="" class="" index="2">最新文章</span></div></div><div class="" style=""><!--[--><!--]--></div><div style="display:none;"><!--[--><!--]--></div><!--[--><!--]--></div><!--[--><!--]--><div id="" class="sidebar-single-enter-animate sidebar-single-common"><div class="sidebar-page"><span class="aurora-iconfont-common aurora-sidebar-message"></span><span>公告</span></div><div class="sidebar-message"><!--[--><li id="sidebar-message" class="sidebar-hover-bg-common"><span>这是一个公告<a href="#">Sakura</a></span></li><li id="sidebar-message" class="sidebar-hover-bg-common"><span>祝大家学业进步</span></li><!--]--></div><!--[--><!--]--></div><!--[--><!--]--><div id="" class="sidebar-single-enter-animate sidebar-single-common"><div id="home-tag-sidebar" class="sidebar-page"><span class="aurora-iconfont-common aurora-sidebar-tag"></span><span>标签</span></div><!----><div class="sidebar-tag-item"><!--[--><a href="/tag?tag=Python" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #8d99ae; font-size: 11px;">Python</span></div></a><a href="/tag?tag=后端" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #b39ddb; font-size: 12px;">后端</span></div></a><a href="/tag?tag=接口" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #84dcc6; font-size: 32px;">接口</span></div></a><a href="/tag?tag=Go" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #b5e2fa; font-size: 29px;">Go</span></div></a><a href="/tag?tag=数据库" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #90f1ef; font-size: 31px;">数据库</span></div></a><a href="/tag?tag=SQL" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #b5e2fa; font-size: 13px;">SQL</span></div></a><a href="/tag?tag=Python基础" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #c0d9dd; font-size: 34px;">Python基础</span></div></a><a href="/tag?tag=前端三剑客" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #5b5f97; font-size: 27px;">前端三剑客</span></div></a><a href="/tag?tag=JavaScript" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #aed9e0; font-size: 23px;">JavaScript</span></div></a><a href="/tag?tag=TypeScript" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #b5e2fa; font-size: 29px;">TypeScript</span></div></a><a href="/tag?tag=Vue" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #00b4d8; font-size: 32px;">Vue</span></div></a><a href="/tag?tag=Element UI" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #fbc4ab; font-size: 19px;">Element UI</span></div></a><a href="/tag?tag=Axios" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #a9def9; font-size: 31px;">Axios</span></div></a><a href="/tag?tag=Scrcpy" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #62b6cb; font-size: 20px;">Scrcpy</span></div></a><a href="/tag?tag=投屏软件" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #ded9e2; font-size: 30px;">投屏软件</span></div></a><a href="/tag?tag=宝塔面板" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #b9faf8; font-size: 32px;">宝塔面板</span></div></a><a href="/tag?tag=同步代码" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #62b6cb; font-size: 19px;">同步代码</span></div></a><a href="/tag?tag=Node" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #1565c0; font-size: 30px;">Node</span></div></a><a href="/tag?tag=持续运行" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #75c9c8; font-size: 31px;">持续运行</span></div></a><a href="/tag?tag=HTTPS" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #7ea8be; font-size: 28px;">HTTPS</span></div></a><a href="/tag?tag=SSL" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #c0d9dd; font-size: 19px;">SSL</span></div></a><a href="/tag?tag=Github" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #fcf6bd; font-size: 33px;">Github</span></div></a><a href="/tag?tag=Gitee" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #9ed8d8; font-size: 14px;">Gitee</span></div></a><a href="/tag?tag=公钥" class=""><div class="sidebar-tag-single"><span class="home-sidebar-tag-hover" style="color: #d8e2dc; font-size: 10px;">公钥</span></div></a><!--]--></div><!--[--><!--]--></div><!--[--><!--]--><!----><!--[--><!--]--></div></div><!--]--></div></div></div></div></div><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--><!--[--><!--]--><div style="--borderRadius:10px;--opacity:1;--fontColor:#2c3e50;--fontFamily:-apple-system;--fitter-blue:1px;" class="sidebar-single-enter-animate footer-layout-center" data-v-4c868354><div class="footer box" id="footer" data-v-4c868354><!--[--><div class="footer-item" id="footer-item" data-v-4c868354><span>Copyright © by sakura All Rights Reserved.</span></div><div class="footer-item" id="footer-item" data-v-4c868354><span><a target='_blank' href='#' style='display:inline-block;text-decoration:none;height:20px;line-height:20px;'><img src='' style='float:left;'/><p style='float:left;height:20px;line-height:20px;margin: 0px 0px 0px 5px;'>💗</p></a></span></div><!--]--><div class="footer-item" id="footer-item" data-v-4c868354><span data-v-4c868354>小破站已运行207天22小时36分2秒</span></div></div></div></div><div id="set-bg" class="set-bg-fitter" style="--opacity: 1; --borderRadius: 10px; --backgroundImageUrl: url()"></div><div id="posterShade" class=""><span class=""></span></div></div><!----><!--]--><!----><div class="no-aurora-music-box aurora-music-box"><div class="music-box"><div class="no-show-more-song music-more"><!--[--><div class="aurora-music-more-single"><span class="aurora-music-song-active aurora-music-font aurora-music-music1 aurora-music-song-info aurora-music-cursor aurora-music-song">SAKURA</span></div><!--]--></div><div class="music-player"><div class="aurora-music-pic-par"><div class="aurora-music-pic" id="aurora-music-pic"><div class="aurora-music-pause"><span class="aurora-music-bofang4 aurora-music-font aurora-music-control-pause aurora-music-cursor"></span></div><img src="/sakura.png" alt=""></div></div><div class="aurora-music-info"><div class="aurora-music-info-left"></div><div class="aurora-music-info-right"><div class="aurora-music-title aurora-music-info-common"><span class="aurora-music-font aurora-music-music1 aurora-music-song-info aurora-music-cursor aurora-music-song">SAKURA</span></div><div class="aurora-music-bottom aurora-music-info-common"><div class="aurora-music-operate"><div><span class="aurora-music-font aurora-music-shangyishou1 aurora-music-cursor"></span></div></div><div class="aurora-music-operate"><div><span class="aurora-music-font aurora-music-xiayishou2 aurora-music-cursor"></span></div></div><div class="aurora-music-operate"><div><span class="aurora-music-geshunxubofang aurora-music-font aurora-music-cursor"></span></div></div><div class="aurora-music-operate"><div><span class="aurora-music-font aurora-music-gengduotianchong aurora-music-cursor aurora-music-operate-more"></span></div></div></div></div></div></div></div><audio autoplay class="aurora-music-player-control" controls="controls"><source src="musicSrc" type="audio/mp3"> Your browser does not support this audio format. </audio></div><div class="aurora-bubble" style="--aurora-bubble-z-index:-2;" data-v-267f56da><div class="aurora-bubble-box" id="aurora-bubble-box" data-v-267f56da></div><canvas class="aurora-bubble-canvas" id="aurora-bubble-canvas" data-v-267f56da></canvas></div><!--]--></div>
    <script src="/assets/js/runtime~app.5b846f34.js" defer></script><script src="/assets/js/452.9ec739fe.js" defer></script><script src="/assets/js/app.05eaf2e4.js" defer></script>
  </body>
</html>
